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

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
>
>
>

=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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Thanks Bob and Joel, the REPT worked a treat.

Regards, Rob

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> =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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>