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! |
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! |
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! |
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. |
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. |
All times are GMT +1. The time now is 12:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com