Vlookup and formatting correctly

[brett]

This has more to do with formatting a text string, but how do i format a
column that already has data in it using "=TEXT(A1,"@")"? This is the only
way i have gotten my vlookup to work. It doesn't work just right clicking
and formatting cells. please help. thanks.
brett

 

[Shane Devenshire]

Hi,

How about a little more detail, what is the formula in A1, what does it
return to A1, what do you want it to look like in the cell with the TEXT
function? Why can't you format cell A1 directly?

What version of Excel are you using?

 

[brett]

A1 is a number. B1 is a Vlookup formula using A1. The result is "#Value".
I have tried to format each column as General, Number, and Text and nothing
works. I can't start over because there is already a ton of data. If i use
"=TEXT(A1,"@")", it works, but how can I format each of the cells in column A
without losing my data?

 

[brett]

I fixed it. I had to use the "=TEXT(A1,"@")" formula for the whole column.
Why would I have to do it that way instead of right clicking and formatting?

 

[Gord Dibben]

In a helper column use the =TEXT(A1,"@") copied down.

Copy that helper column and Paste Special>Values onto Column A

Delete the helper column.


Gord Dibben MS Excel MVP