Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add Zeros to Numbers
Greetings. I'm using EXCEL 2003, and I have a problem that I can't seem to
find the answer to. I have a column that has 15506 rows, and each row contains a number, an underline, and a word. The rows look something like this: 1_blah 2_blahblah 3_cat 4_dog .... 234_flower 235_flowing .... 15500_zebra 15501_zemo etc. I need to put four zeros in front of numbers 1-9, three from 10-99, etc., so I can later sort on this column when I'm using the data in another program (please don't ask why the data is like this; it just is!). Is there a way to add these zeros automatically before the words but without having to do any programing, something I am unable to do? I would deeply appreciate your help in solving this problem. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add Zeros to Numbers
=REPT("0",6-FIND("_",A1))&A1
Copy down as far as needed. -- Kind regards, Niek Otten "Rebecca" wrote in message ... Greetings. I'm using EXCEL 2003, and I have a problem that I can't seem to find the answer to. I have a column that has 15506 rows, and each row contains a number, an underline, and a word. The rows look something like this: 1_blah 2_blahblah 3_cat 4_dog ... 234_flower 235_flowing ... 15500_zebra 15501_zemo etc. I need to put four zeros in front of numbers 1-9, three from 10-99, etc., so I can later sort on this column when I'm using the data in another program (please don't ask why the data is like this; it just is!). Is there a way to add these zeros automatically before the words but without having to do any programing, something I am unable to do? I would deeply appreciate your help in solving this problem. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting numbers to text | New Users to Excel | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
csv file drops zeros at beginning of a set of Numbers | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) |