Removing leading zeros from a group of numbers

[Guest]

I have a report that is imported from another source. A column has numbers
showing as 00047, formatted as "general". I need to use those numbers in a
vlookup formula - and the lookup table has the numbers without the leading
zeros. So right now, nothing is pulling. Is there a way to get rid of those
leading zeros - either by formatting or with a formula - so I can avoid
re-entering them all by hand (there are about 1500 of them). I use Excel 2000

 

[Guest]

In an un-used cell, enter 1. Copy this cell. Select all the cells that you
want to fix. Paste/Special with the multiply button checked.

 

[Guest]

One way is to use a helper column and enter =A1*1 and copy down......

Then Copy > PasteSpecial > Values on that column to eliminate the formulas

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

If the values all look like numbers, you could change your =vlookup() formula to
look like:

=vlookup(--a2,sheet2!a:b,2,false)

The -- stuff will coerce the text number to a real number.

-"00047" becomes -47
--"00047" becomes the opposite of -47 or +47 (= 47).