Pad Numeric Portion Of Text & Number With Zeros

  • Hi All,
    I build data for the Tom Tom navigator which ends up in an Excel spreadsheet as follows
    Cell A1 contains 40 Fazakerly
    Cell A2 contains 196 Anfield
    Cell A3 contains 4392 W.Derby
    Cell A4 contains 4643 Fairfield 1

    The number and name are in the same individual cells. Can I somehow re-format the cells so if only 2 or 3 numerical digits are present as in the first and second Cells they are padded out with zeros to 4 digits. For example

    Cell A1 now contains 0040 Fazakerly
    Cell A2 now contains 0196 Anfield
    Cell A3 now contains 4392 W.Derby
    Cell A4 now contains 4643 Fairfield 1

    Thanks in advance for any assistance given.

    Paul T

  • Re: Cell Format Adding Leading Zeros

    It works if only numbers in the cell
    ie 40 is converted to 0040
    but not if numbers and letters
    ie 40 Fazakerly stays the same 40 Fazakerly

  • Re: Cell Format Adding Leading Zeros

    =rept(0,5-find(" ",a1))&a1
    If there are any addresses that have more than 4 digits, =rept(0,Max(0,5-find(" ",a1)))&a1

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!