Add leading 0

[ekhawaja]

Hello, looking for a formula to add a leading 0 if there is no 0 at the beginning of text:
Have: Want: Formula

05MS2030	05MS2030
05MS2031	05MS2031
5MS2039	05MS2039
5MS2040	05MS2040

The length of text varies, but need a "0" if there is no "0" present initially.
thanks in advance!

 

[dreid1011]

How about this:

Book1
	A	B
1	05MS2030	05MS2030
2	05MS2031	05MS2031
3	5MS2039	05MS2039
4	5MS2040	05MS2040
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=IF(--LEFT(A1,1),"0"&A1,A1)

However, if the first letter/number is not a 0 or a number, then you should use this:

=IF(LEFT(A1,1)<>"0","0"&A1,A1)

Edit: That also begs the question, could you have a value like this where you would want to add another leading 0? "0MS2050" to become "00MS2050"

 

[ekhawaja]

Hello, looking for a formula to add a leading 0 if there is no 0 at the beginning of text:
Have: Want: Formula

05MS2030	05MS2030
05MS2031	05MS2031
5MS2039	05MS2039
5MS2040	05MS2t

How about this:

Book1
	A	B
1	05MS2030	05MS2030
2	05MS2031	05MS2031
3	5MS2039	05MS2039
4	5MS2040	05MS2040
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=IF(--LEFT(A1,1),"0"&A1,A1)

However, if the first letter/number is not a 0 or a number, then you should use this:

=IF(LEFT(A1,1)<>"0","0"&A1,A1)

Edit: That also begs the question, could you have a value like this where you would want to add another leading 0? "0MS2050" to become "00MS2050"

thank you so much, it works!

 

[dreid1011]

You're welcome, and happy to help.