Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading zeroes formula
Hi everyone. In column B I have a list of numbers. Each number
varies in length, but no number is longer than 6 cbaracters. I need a formula that will make each number rxactly 6 characters by attaching leading zeroes to the FRONT if the number is less than 6 characters, or do nothing if the number is already 6 characters. The "number" can be converted to text. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading zeroes formula
Hi,
Select the cell(s) and then Format|cells - Custom enter a custom format of 000000 That's 6 zeroes. Mike "Steve" wrote: Hi everyone. In column B I have a list of numbers. Each number varies in length, but no number is longer than 6 cbaracters. I need a formula that will make each number rxactly 6 characters by attaching leading zeroes to the FRONT if the number is less than 6 characters, or do nothing if the number is already 6 characters. The "number" can be converted to text. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading zeroes formula
=IF(LEN(A1)<6,REPT("0",6-LEN(A1))&A1,IF(LEN(A1)=6,A1,""))
HIH On 18 Mar, 18:20, Steve wrote: Hi everyone. *In column B I have a list of numbers. *Each number varies in length, but no number is longer than 6 cbaracters. *I need a formula that will make each number rxactly 6 characters by attaching leading zeroes to the FRONT if the number is less than 6 characters, or do nothing if the number is already 6 characters. *The "number" can be converted to text. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading zeroes formula
of course I meant
=IF(LEN(B1)<6,REPT("0",6-LEN(B1))&B1,IF(LEN(B1)=6,B1,"")) HIH On 18 Mar, 19:56, Jarek Kujawa wrote: =IF(LEN(A1)<6,REPT("0",6-LEN(A1))&A1,IF(LEN(A1)=6,A1,"")) HIH On 18 Mar, 18:20, Steve wrote: Hi everyone. *In column B I have a list of numbers. *Each number varies in length, but no number is longer than 6 cbaracters. *I need a formula that will make each number rxactly 6 characters by attaching leading zeroes to the FRONT if the number is less than 6 characters, or do nothing if the number is already 6 characters. *The "number" can be converted to text. Thanks!- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leading zeroes in ZIP field | Excel Discussion (Misc queries) | |||
Sorry, leading zeroes again :-(( | Excel Discussion (Misc queries) | |||
Leading Zeroes | Excel Discussion (Misc queries) | |||
Leading Zeroes | Excel Discussion (Misc queries) | |||
CSV leading zeroes | Excel Discussion (Misc queries) |