Best way to pad a number with leading zeros

for a base 10 number you can do this... assuming the original number is in column A, you can place the padded number in column B:

B2=NUMTOBASE(A2, 10, 7)

this is shorthand for…

select cell B2, then type (or copy and paste from here) the formula:

=NUMTOBASE(A2, 10, 7)

To fill down,

select cell B2, copy

select cells B2 thru the end of column B, or as needed, paste

crashmeister wrote:

I am extracting a time value from a date. I want the result as a string in HH:MM format.

This means that each component (HOUR and MINUTE) needs to be padded to two characters, so I get 06:05 and not 6:5.

A more specific solution to your post, which asks how to extract HH:MM format from a date, would be something like this:

A valid date-time is in cell A2. The formula in B2, which extracts the time and formats with padding, is:

=RIGHT("0"&HOUR(A2),2)&":"&RIGHT("0"&MINUTE(A2),2)

The result in B2 is in HH:MM format.

SG