zero supress leading zeros when chg format from text to number

[Guest]

I am trying to do a vlookup between 2 spreadsheets and in the one the data is
stored as text and the other as a number with leading zeros (ie. 001) when I
change the format to text it keeps the 001 so I was just wondering if there
is some code to strip the leading zeros out before I reformat the numbers to
text. Your help is appreciated.
TIA
Heather

 

[Guest]

Actually the 2 spreadsheets have the same format as number with 0 decimal
places in one of the spreadsheets though the data is stored as '001 and the
other just as 1. The problem is I can not change the data in the one that
has the '001 since it is accessed from another program looking for the 001.
Any solutions would be appreciated like is there away to get rid of the
leading '00 and not anything after since the numbers do go from 1 to 45 '045
and there are some like '040.
TIA
Heather

 

[Guest]

Hi Heather,

I think your best bet would be to format your lookup value to match the
sheet you are searching, i.e., your search criteria should change between
numeric and text.
I hope this makes sense.. If not, I will clarify

Good Luck,
GG

 

[HighTide]

There are several ways to do this
=value(A1) will drop the zeroes
=""&value(A1) or =text(value(A1),0)
Converts it back to text.

Does this help?
You will still have match text in your functions.
For examaple =match(1,range,0) will return #N/A
but =match("1",range,0) will find your text version of 1 after you
strip the zeroes.

 

[Guest]

Thanks HighTide and GG,
I'll give it a try, your help is much appreciated.