zero supress leading zeros when chg format from text to number

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

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

"HeatherO" wrote:

> 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

You could use Find and Replace: put 00 in Find and leave Replace blank. Choose Replace All. While a single 0 will remain safe, 001 will become 1, 0020 will become 20, but anything like 006000 would become 60. So be careful. Any error and click on Edit / Undo Replace to return to the start.

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

"HeatherO" wrote:

> 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
>
> "HeatherO" wrote:
>
> > 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

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.


On Sat, 26 Feb 2005 12:49:02 -0800, "HeatherO"
<[email protected]> wrote:

>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

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

"HighTide" wrote:

> 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.
>
>
> On Sat, 26 Feb 2005 12:49:02 -0800, "HeatherO"
> <[email protected]> wrote:
>
> >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
>
>