Add leading zeros fill space
 

Hi,

Using Excel 2000

I have spreadsheet that uses text entries to do a Vlookup, the look up range
has text in the format of 6 characters albeit they look like numbers with
leading zeros eg. '000100. The entry that I enter if '000100 returns the
correct result. However, when others enter they tend to just enter '100
which doesn't match. I therefore thought that if I could check the length
of their entry, I could fill with leading zeros to make it 6 characters
long.

Having tried to do this with validation, I'm now thinking this is a change
cell piece of code.

Any pointers of sample code would be appreciated.

Thank you, Rob

Add leading zeros fill space
 

using a worksheet function

=REPT(0,6-LEN(A1))&A1

using VBA

Sub test()

num = 100
longstr = String(6 - Len(A1) & A1, "0")
End Sub
"Rob" wrote:

Hi,

Using Excel 2000

I have spreadsheet that uses text entries to do a Vlookup, the look up range
has text in the format of 6 characters albeit they look like numbers with
leading zeros eg. '000100. The entry that I enter if '000100 returns the
correct result. However, when others enter they tend to just enter '100
which doesn't match. I therefore thought that if I could check the length
of their entry, I could fill with leading zeros to make it 6 characters
long.

Having tried to do this with validation, I'm now thinking this is a change
cell piece of code.

Any pointers of sample code would be appreciated.

Thank you, Rob

Add leading zeros fill space
 

=VLOOKUP(REPT("0",6-LEN(A1))&A1,L1:P9,2,FALSE)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rob" wrote in message
...
Hi,

Using Excel 2000

I have spreadsheet that uses text entries to do a Vlookup, the look up
range has text in the format of 6 characters albeit they look like numbers
with leading zeros eg. '000100. The entry that I enter if '000100 returns
the correct result. However, when others enter they tend to just enter
'100 which doesn't match. I therefore thought that if I could check the
length of their entry, I could fill with leading zeros to make it 6
characters long.

Having tried to do this with validation, I'm now thinking this is a change
cell piece of code.

Any pointers of sample code would be appreciated.

Thank you, Rob

Add leading zeros fill space
 

Thanks Bob and Joel, the REPT worked a treat.

Regards, Rob

"Bob Phillips" wrote in message
...
=VLOOKUP(REPT("0",6-LEN(A1))&A1,L1:P9,2,FALSE)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Rob" wrote in message
...
Hi,

Using Excel 2000

I have spreadsheet that uses text entries to do a Vlookup, the look up
range has text in the format of 6 characters albeit they look like
numbers with leading zeros eg. '000100. The entry that I enter if
'000100 returns the correct result. However, when others enter they tend
to just enter '100 which doesn't match. I therefore thought that if I
could check the length of their entry, I could fill with leading zeros to
make it 6 characters long.

Having tried to do this with validation, I'm now thinking this is a
change cell piece of code.

Any pointers of sample code would be appreciated.

Thank you, Rob