Explanation
The TEXT function can apply number formats of any kind, including currency, date, percentage, etc. By applying a number format like “00”, “000”, “0000”, you can “pad” numbers with as many zeros as you like. Zeros will only be added where needed.
Number format only
The TEXT function converts numbers to text as a normal step in applying the number format.
If you are concatenating (joining) the result to another text string, this is fine, but if you just want to apply visual padding to a free-standing number set of numbers, you can apply a custom number format without using a formula.
Explanation
In this example, the goal is to parse a text string into a proper Excel time .
First, note that the cells in F5:F13 are formatted as Text prior to entry . This allows the times to contain leading zeros like “083000”. Alternately, you can enter these time strings with a single quote at the start (’) to force Excel to respect them as text.
Next, the time string contains 6 characters in the following format:
hhmmss // as text
This means the formula needs to pick up 2 characters each for hour, minute, and second. Working from the inside out, this task is performed with the LEFT, MID, and RIGHT functions:
LEFT(E5,2) // get hh
MID(E5,3,2) // get mm
RIGHT(E5,2) // get ss
Each of the functions returns a result directly to the TIME function. In E9, we have:
041055
So the result inside TIME looks like this:
=TIME("04","10","55")
The TIME function then quietly handles the text-to-number conversion and returns a valid time:
04:10:55
Representing 4 hours, 10 minutes, and 55 seconds.
With a 4-character time string
The formula in C5 is meant to handle only a 4 character time string (hours and minutes), so the structure is a bit simpler. We simply hardcode the value for seconds into the TIME function as zero:
=TIME(LEFT(B5,2),MID(B5,3,2),0)
