Forum Discussion
Convert 1234 to 12:34
Good morning!
Could someone help me in converting a large series of 4 and 3 digit numbers into a new form are which contains “:”.
As these examples:
1234 to 12:34
333 to 3:33
4567 to 45:67
555 to 5:55
and so on...
I remember that there was a function helping with this conversion but it can't remember which one.
Thank you very much in advance, Juan
5 Replies
Hi Juan,
If you'd like to convert such numbers into time when
=INT(A1/100)/24+MOD(A1,100)/24/60
preliminary formatting target column as [hh]:mm. Number 4567 will be converted to 46:07 (60 minutes in an hour).
If convert to text when
=LEFT(A1,LEN(A1)-2) & ":" & RIGHT(A1,2)
Thank you very much for your quick answer.
Unfortunately when I copy this formula into my Excel 2016 (in Spanish) it is not accepted.
Maybe I do have to change "," for ":"?
Juan,
You may translate formulas from one language to another using this tool https://en.excel-translator.de/translator/
They'll be
=ENTERO(A1/100)/24+RESIDUO(A1;100)/24/60
and
=IZQUIERDA(A1;LARGO(A1)-2) & ":" & DERECHA(A1;2)
If you open attached file formulas will be in your locale notation.
