Formatting a cell to contain 8 characters

[Johnny Thunder]

Hello friends,


I am trying to run a lookup on a row of data that came out with one character in each cell, my lookup table has the same characters but has 7 preceeding zeros depending on the number.

In total all cells contain an 8 character cell but using Format Cell>Special>00000000 does not work, it only makes the cells look correct but when you select a cell it still appears as a one character cell????

Anyone know how to resolve?

Some of the numbers I need to make 8 characters cells.

9
12
22
5

Needs to be

00000009
00000012
00000022
00000005


Excel 2003

 

[barry houdini]

If the lookup table is text formatted you can use a TEXT function on the lookup value, something like this

=VLOOKUP(TEXT(A1,"00000000"),B1:C10,2,0)

 

[T. Valko]

Hello friends,


I am trying to run a lookup on a row of data that came out with one character in each cell, my lookup table has the same characters but has 7 preceeding zeros depending on the number.

In total all cells contain an 8 character cell but using Format Cell>Special>00000000 does not work, it only makes the cells look correct but when you select a cell it still appears as a one character cell????

Anyone know how to resolve?

Some of the numbers I need to make 8 characters cells.

9
12
22
5

Needs to be

00000009
00000012
00000022
00000005


Excel 2003

Try it like this...

TEXT(A1,"00000000")

In a lookup formula...

=VLOOKUP(TEXT(A1,"00000000"),J:K,2,0)

 

[Johnny Thunder]

Perfect!

Thanks for your help guys! It worked perfectly!

 

[T. Valko]

Perfect!

Thanks for your help guys! It worked perfectly!

You're welcome. Thanks for the feedback!