Add padded zeroes to value in a cell?
Posted by Marc on May 24, 2001 4:11 PM
When I use VLOOKUP to compare the value in column A with column E, I don't get a match, because the value in A doesn't show as 11 digits. Some of the data has been padded with 4, 3, 2, or 1 zero. Is there a way to take the data as displayed in the column using a custom cell format and paste or convert it to the full 11 digit value? Example: Cell A2 displays 00002103910, the value is 2103910, and the data value in Column E is 00002103910. Hence no match.
Posted by Brian P on May 24, 2001 4:38 PM
Is one text and the other value format?
You could do a text to column on the one with the zeros - to remove the leading zeros
Posted by Marc on May 24, 2001 4:44 PM
Both are formatted the same, but come from different sources ... I tried text to column on the searched solumn ... lead zeroes stayed .
Posted by cpod on May 24, 2001 4:46 PM
You could try something like this:
=VLOOKUP(TEXT(A1,"00000000000"),$E$1:$F$100,2)
Posted by Aladin Akyurek on May 24, 2001 5:14 PM
The following appaers to work, but it may incur some performance cost.
Lets assume that we have the following lookup table, the first column of which consists of numbers with leading zeroes occupying the range C1:D4.
{"0012","a";13,"g";"00078","f";"0098","v"}
Lets also assume that we have a bunch of lookup values for which we want retrieve the associated values from the foregoing lookup table. These are also numbers with leading zeroes, occupying the range A1:A4.
{"012";"78";"013";"098"}
The following array-formula retrieves an associated value if exists:
=IF(ISNUMBER(MATCH(VALUE(A1),VALUE($C$1:$C$4),0)),INDEX($D$1:$D$4,MATCH(VALUE(A1),VALUE($C$1:$C$4),0),1),"") [ copied down ]
I get the following results:
{"a";"f";"g";"v"}
Hope this helps.
Aladin
=============
Posted by Dave Hawley on May 24, 2001 7:07 PM
Hi Marc
I would look into cpods response, I just tried it with this data.
A2= 25689 with a custom format of "0000"0
it displays 000025689. While its value is 25689
I formatted E4 as Text and put in: 000025689
I then used: =VLOOKUP(TEXT(A2,"000000000"),E1:F16,2,FALSE)
and it returned the expected result.
If you do not need the zeros, type a 1 in any cell and copy it, then higlight your data and Edit>PasteSpecial-Multiply over the top. This will convert your data to real numbers and remove the leading zeros.
Dave
OzGrid Business Applications
Posted by Marc Hennebery on May 24, 2001 7:09 PM
The Column to Text worked when I used it on the column being searched. It still displays the lead zeroes, but the values changed to a format that matched ... Thanks to everyone for their help.
.. Marc
