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

Copy that helper column and Paste SpecialValues onto Column A

Delete the helper column.


Gord Dibben MS Excel MVP

On Tue, 23 Jun 2009 11:14:01 -0700, brett
wrote:

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?

"Shane Devenshire" wrote:

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?
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"brett" wrote:

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