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

That works just great. MANY Thanks. Can incorporate it into my macro.

Paul T