Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leading Zeros | Excel Worksheet Functions | |||
leading zeros | Excel Discussion (Misc queries) | |||
How do I make a number have leading zeros to fill width of cell? | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) |