Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Adding Leading Zeros to Text
In column A I have text like the following
1.2 01.34 03.4 2.1 What I need excel to do is look in Column A and any text that does not begin with a zero character, add a zero to the beginning of the text string, else leave it alone. So that my above example when done would look like: 01.2 01.34 03.4 02.1 Any quick fixes for this? I have a thousand rows so doing this by hand would really stink! Thanks in advance! |
#2
|
|||
|
|||
How about adding a formula of 00.0#?
-- HTH RP (remove nothere from the email address if mailing direct) "Jenn" wrote in message ... In column A I have text like the following 1.2 01.34 03.4 2.1 What I need excel to do is look in Column A and any text that does not begin with a zero character, add a zero to the beginning of the text string, else leave it alone. So that my above example when done would look like: 01.2 01.34 03.4 02.1 Any quick fixes for this? I have a thousand rows so doing this by hand would really stink! Thanks in advance! |
#3
|
|||
|
|||
sorry, meant custom format not formula.
-- HTH RP (remove nothere from the email address if mailing direct) "Jenn" wrote in message ... In column A I have text like the following 1.2 01.34 03.4 2.1 What I need excel to do is look in Column A and any text that does not begin with a zero character, add a zero to the beginning of the text string, else leave it alone. So that my above example when done would look like: 01.2 01.34 03.4 02.1 Any quick fixes for this? I have a thousand rows so doing this by hand would really stink! Thanks in advance! |
#4
|
|||
|
|||
This formula did it for me:
=TEXT(VALUE(A1),"00.00") Working from the inside out, this formula converts your text to a value to standardize each entry so it does not have a leading zero. Then the formula converts it back to text in 00.00 format- this to accommodate the 2 decimal places required as in the 01.34 example. |
#5
|
|||
|
|||
Thanks! Both worked like a charm!
"Dave O" wrote: This formula did it for me: =TEXT(VALUE(A1),"00.00") Working from the inside out, this formula converts your text to a value to standardize each entry so it does not have a leading zero. Then the formula converts it back to text in 00.00 format- this to accommodate the 2 decimal places required as in the 01.34 example. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding cells, using text as number | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
How do I display leading zeros so I can export a fixed in Excel? | Excel Discussion (Misc queries) | |||
numbers and text in Excel to read as text keeping the leading zer. | Excel Discussion (Misc queries) | |||
Adding a text label to a line. | Charts and Charting in Excel |