FORMULA TO PASTE SPECIAL (VALUE)

[meppwc]

I have a formula in B2, =LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),3))-1) that returns 10.13.126. This works as it should.
In C1 I need a formula that populates with 10.13.126. This would be the equivalent of manually copying from B2 and selecting Special - Value.
Is there such a formula?

 

[Joe4]

No, a cell can either have a formula or a hard-coded value in it, but never both at the same time.
And one cannot automatically change to the other without the use of VBA.
So if you do not want to do that manually, the only way to convert it is to use VBA.

I am curious though - why does it need to be hard-coded?
Why is it a problem for the formula to return the value?
Are the underlying values used in the formula changing (i.e. cell A2), and you want to retain the original value?

 

[meppwc]

My reason for this is because I want to convert 10.13.126 to 10.13.126.1-254, by using =C1&".1-254"

 

[Joe4]

You don't need to convert it to hard-coded formula. Just add that piece on to the end of what you have now, i.e.

=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),3))-1)&".1-254"

or just reference the cell that has the first part of that formula, i.e.

=B2&".1-254"

 

[meppwc]

Oh......that was a simple fix..........thanks Joe4

 

[Joe4]

You are welcome.

Yep, it doesn't matter if the value is hard-coded or derived from a formula, both can be used in other formulas.