Ad 0 to cell with both nuber and text

[chrisdue]

Hi!

I have loads of cells containing a zip code + area name

The 0 in Area codes starting with "0" have been removed, so a lot of the cells have three digits + area name

How do I add a 0 to all cells that only have three digits + text in the same cell?

Thanks alot!

[liz5818]

Have you tried changing the format of that column to text instead of number?

[Ace_XL]

Try

=IF(ISNUMBER(LEFT(A1,4)*1),A1,0&A1)

[Bhuvi]

Hi Chrisdue,

It would be much better if you can attach a sample sheet with some data.

[chrisdue]

Thanks Ace, you almost solved my problem!

Problem now is that the cells with 4 digits allready also add a 0

eks bilde.jpg

Any idea?

Thanks again!

[Rambo4711]

Use this formula in N2:

=IF(LEN(LEFT(M2,FIND(" ",M2,1)-1))=3,0&M2,M2)

Copy down