Text join formulas working but act as straight text when modified

[akkrug]

I have a spreadsheet in which I am using left and right functions to populate
a new column. This seems to work if I key the whole thing in at once. When
I try to modify the formula, it stops working and displays as straight text.
Format of all cells is general. The formula in question is as follows:
=LEFT(B5,2)&RIGHT(B5,2)&(A5). It works after I key it, but if I key any
change to it, it displays as text only and stops working.

Thanks for the help!

Ken K. --
akkrug

 

[Bernie Deitrick]

Ken,

The behavior you are describing occurs when a cell has a Text format applied after the entry of the
formula. The formula will work as long as it is not edited, but the cell will then take on the Text
format (ie, will show the formula as a string) when it is edited. Make sure that you reapply the
General formatting, then press F2 and press Enter to re-enter the formula.

HTH,
Bernie
MS Excel MVP

 

[akkrug]

Thanks Bernie!!
--
akkrug

Ken,

The behavior you are describing occurs when a cell has a Text format applied after the entry of the
formula. The formula will work as long as it is not edited, but the cell will then take on the Text
format (ie, will show the formula as a string) when it is edited. Make sure that you reapply the
General formatting, then press F2 and press Enter to re-enter the formula.

HTH,
Bernie
MS Excel MVP

 

[Robert McCurdy]

=LEFT(B5,2)&RIGHT(B5,2)&(A5).

Assuming these cells are numerical...

=--(LEFT(B5,2)&RIGHT(B5,2)&A5)

This should display as a number - if that is your intent?


Regards
Robert McCurdy