Formating a Cell

[Suresh Kusunuri]

Hi All,

I have codes like “29ABCDE1234X2YZ” or “2ABCDE1234X2YZ”. The code should be total 15 Digits/Letters.
In the second code it contains 14 Digits/Letters only.
Can anybody suggest me to prefix “0” before 2 by formatting the cell (without using formula or function).
Because I want to use a formula by using LEFT(___,2).
I used “000000000000000” in custom format. But it’s not worked.

Thanks in Advance
Suresh

 

[footoo]

Maybe : =LEFT(REPT("0",15-LEN(A1)) & A1,2)

 

[Fluff]

If you format the cell, you don't actually change the value of the cell, therefore you would not be able to use left(x,2).
So you will have to change the cell value.

 

[footoo]

So you will have to change the cell value.

Or expand the formula per post #2.

 

[Rick Rothstein]

Maybe : =LEFT(REPT("0",15-LEN(A1)) & A1,2)

Another formula that can be used...

=LEFT(RIGHT(0&A1,15),2)

 

[footoo]

Another formula that can be used...

=LEFT(RIGHT(0&A1,15),2)

Note : This formula and the formula in post #2 do not return the same result for less than 14 characters.

 

[Rick Rothstein]

footoo is correct.... I assumed from the OP's original message that users were only putting in 14 or 15 characters. If it is possible for a user to put in less and the OP would want "00" returned for them, then this modification to my formula (modeled after footoo's formula in Message #2, just applied differently) should work...

=LEFT(RIGHT(REPT(0,15)&A1,15),2)

 

[Peter_SSs]

I think 2 zeros should be sufficient to prefix since only 2 characters are required in the end result.

=LEFT(RIGHT("00"&A1,15),2)

 

[sandy666]

maybe =BASE(IFERROR(--LEFT(A1,2),--LEFT(BA1,1)),10,2)
of course this is longer than above

edit: it doesn't work on XL2007, I overlooked version

 

[Suresh Kusunuri]

Thanks a lot all of you.
Mr. Rick Rothstein & Mr. Peter SSs formulas are working perfectly for me.
Thanks both of you for suggestions and Formula.
Suresh