Google-sheets – How to format a column of 3 and 4 digit numbers to add a 0 in front of any 3 digit numbers so they are all in 4 digit format? Google sheets

google sheets

How do I format a column of 3 and 4 digit numbers to add a 0 in front of any 3 digit numbers so they are all in 4 digit format? Google sheets I have a time function formula I'm applying to a series of times that are entered as either 836 or 1236 etc, but for my function to work I need to go back and easily convert each column to have all entries in 4 digit format so the 0836 needs to be converted to 0836 visually as plain text without manually updating each individual entry… Thanks!

Best Answer

"...format a column of 3 and 4 digit numbers to add a 0 in front of any 3 digit numbers so they are all in 4 digit format?"

You can use the following formula. 

=ArrayFormula(IF(A2:A22<>"", (TEXT(A2:A22,"0#:##")),""))

(In the above given formula we assume that your range is A2:A22. You should change that to your needs.)

To change the results to time and further use them as time in other formulas please use: 

=ArrayFormula(IFERROR(IF(A2:A22<>"", (TEXT(A2:A22,"0#:##"))," ")*1))

You could also format the results as time.

Functions used:

Related Topic