0

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!

0

"...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:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.