#1
|
|||
|
|||
adding zeros before a number depending on the number of digits
hello
i'm ron from ISRAEL and i have a question as a beginner. i've a list of costumers. with about 5 columns. the problematic column is the costumer number column. at the beginning of each customer number there should be two or three zeros. for example if it is 345 it should have two zeroes before the 345. if the number is 3456 it should have three zeros before the 3456. is it complicated to add the zeros to all 500 customers? thank you love |
#2
|
||||
|
||||
Select the customer column
Right Click - Format Cells - Number Tab -Select Custom In the box under "Type" enter 00000 ( five zeroes) - ok |
#3
|
|||
|
|||
i will try soon.....
but i want clarify... it should distinguish between a costumer number of 3 digits and a costumer number of 4 digits and add the zeroes accordingly..... thanks |
#4
|
||||
|
||||
Sorry I misunderstood. Perhaps
Code:
=if(LEN(A1)=3,TEXT(A1,"00000"),TEXT(A1,"0000000")) This only changes the formatting, the underlying value will not change If you need to use the obtained string then Code:
=IF(LEN(A1)=3,"00"&A1,"000"&A1)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
thank you
i will try it very soon when i get to the computer.. |
#6
|
|||
|
|||
I have had to do this for the last 20 years on SEDOL codes and this works perfectly
=REPT("0",5-LEN(J16))&J16 Probably my fav formula! |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Remove trailing zeros from number format but only for a zero value | Matt C | Excel | 3 | 11-22-2021 06:46 AM |
Merge field return last 4 digits of a number | macquhele | Mail Merge | 6 | 08-01-2020 11:04 PM |
Entering a number of 19 digits | Jo Freeman | Excel | 11 | 11-08-2017 06:20 AM |
Having number's digits together | mohsen.amiri | Word | 0 | 06-23-2017 01:20 AM |
How to create a table with a number of line depending a number entered by user | Cellendhyll | Word Tables | 3 | 07-10-2014 05:49 AM |