Text join formulas working but act as straight text when modified

  • Thread starter Thread starter akkrug
  • Start date Start date
A

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
 
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
 
Thanks Bernie!!
--
akkrug


Bernie Deitrick said:
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
Click to expand...
 
=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
 
Back
Top