Add 0 to start of number if length is less than 5

Set the number format of the relevant columns to be Custom and "00000"

oh hold on, so if its 3 chars then it stillonly gets 1 zero appended?

To borrow from Lois Lowry's "The Giver", can we be very precise in our language? When you talk about "adding a 0 character to the left of a number" exactly what are you wanting to happen -- just display the number with those insignificant zeroes while the underlying value stays the same, or do you need to convert the number to a text string with those zeroes as part of the cell value? I notice that all of the proposed solutions are changing the number formatting so that the number 901 looks like 0901, but the cell's value is still 901. Do you need the cell value to stay 901, or do you need the cell's value to become the text string "0901"?

When the number 901 becomes the text string "0901" what will then happen? We frequently encounter issues with numbers stored as text. Most of the time, the solution involves storing numbers as numbers. Converting numbers to text shouldn't be overly difficult, but it could be valuable to think through exactly how you are going to work with these "numbers stored as text" and otherwise how you are interacting with the spreadsheet to be sure that storing numbers as text is the best solution.

You say that the data from elsewhere "shows" numbers as 0901 or 01901. Does this mean that the numbers are stored as text in this other location, or are they the number 901 formatted as "0000" or 1901 formatted as "00000"? Or are these data from elsewhere not even in a spreadsheet? How are you performing the lookup operation (Excel lookup function or something else)? Assuming the "data from elsewhere" is a number stored as text, would it be preferred to convert that to a real number? It often doesn't matter whether you search for numbers stored as numbers or numbers stored as text as long as you are consistent, which often leads to a decision as a programmer whether you will find it easier to consistently store everything as text or consistently store everything as number.

Try this:

PHP Code: 

Option Explicit
Sub test()
Dim lr&, lc&, LCell As Range
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Set LCell = Cells(lr, lc)
With Range("D2", LCell)
    .NumberFormat = "@"
    .Value = Evaluate("=IF(LEN(D2:" & LCell.Address & ")<5,IF(LEFT(D2:" & LCell.Address & ",1)<>""0"",""0""&D2:" & LCell.Address & ",D2:" & LCell.Address & "),D2:" & LCell.Address & ")")
    .Replace 0, "", xlWhole
End With
Application.ScreenUpdating = True
End Sub 


Please Login or Register  to view this content.