I have to double click a cell for the "text" format to take

[Charles]

I'm using a VLOOKUP table with 26,000 six digit coded items. When I use
format cells - text, it doesn't take until I actually double click on the
"text" cell. I can't do this for 26,000 items, I'll go crazy! I tried using
the formula text(A1,"000000") but it doesn't work - just shows the formula as
a text expression in the cell!

[David McRitchie]

Hi Charles,
By double clicking you have put the cell into edit mode, same or similar to use of F2,
but you still have to hit Enter which is reentering the content. I presume you originally
had a number in the cell and were trying to format it to text by formatting the
column as text. Excel will not switch between numbers and text formats until reentered.
use of =ISTEXT(A1) or =ISNUMBER(A1) will show you what Excel thinks
your data is. Test with F2 then Enter is a good way to quickly check for things like
data is not as formatted for new data, or that calculation is turned off.
Tools menu, Options, calculation, automatic
of course F9 or one of its other variations would also recalculate
http://www.mvps.org/dmcritchie/excel/ shortx2k.htm#calc -- shortcut keysl


I have several macros on my join.htm that will do some form of reentry, but the
easiest for me is to run my TrimALL macro, which trims the data and
reenters it so that is what I would try first.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

I would install the macro as that is the easiest way to go and fixes a lot of problems
for the things that I do. But for a non macro solution you could reenter as follows.

Format the range preferably entire columns that you want to convert to text or number.
Select an empty cell (no content, no spaces, no formulas must test true for =ISBLANK(ref)
Select the range you want to fix, can be the entire column(s)
Edit, Paste Special, Add

Whatever method you try would suggest you try this on a copy of your worksheet.
since it is unfamiliar to you. Also suggest you set up a column alongside
where you can test what Excel things of your data. =ISTEXT(A1)

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Charles" <[email protected]> wrote ...
> I'm using a VLOOKUP table with 26,000 six digit coded items. When I use
> format cells - text, it doesn't take until I actually double click on the
> "text" cell. I can't do this for 26,000 items, I'll go crazy! I tried using
> the formula text(A1,"000000") but it doesn't work - just shows the formula as
> a text expression in the cell!

[David McRitchie]

Hi Charles,
I left out the step to copy the Empty cell in the instructions
to effect a reentry for a selection of cells.

Select an empty cell (no content, no spaces, no formulas
to use cell M1 it must test True for =ISBLANK(M1)
Copy that cell, then
Select the range you want to fix, can be the entire column(s)
Edit, Paste Special, Add