vlookup formula editing based on numbers treated as text

[Guest]

I've several xref tables in a spreadsheet that I decode using vlookup
I've used the following which worked...
=VLOOKUP(TEXT(DlrID,"0000"),DealerNameXREF!A2:B4672,2,FALSE)

however when I edit the formula suddenly the cell contents become the
formula and not the formula value

using Edit> Replace> I can replace specific parts of the formula to what I
want and that had worked

but a new version of the XRef tables (which I copied in as seperate
worksheet) which has all numbers formatted as text (to preserve leading
zeros) isn't allowing me to create functioning formulas

even =3+4*8 in a cell displays as "=3+4*8" and not the value

ctrl + ` doesn't change anything related to the cell formulas I'm working with

where is the sheet parameter that I'm missing that may have been inherited
from the copied in worksheet?

most of the lookup references are based on numeric values zero padded and
treated as text

 

[Guest]

Change the format of the cells to General (Ctrl-F1, Number tab)
At the moment the cells are formatted as text, so that when you edit a
formula it displays the actual formula, not the result