Create excel time out of 2 to 4 digit number

Dear fellow excellers, I have a bunch of number values representing timestamps, in the format 705 1440 41 I need to convert them to proper excel time format, i e 07:05 14:40 00:41 Have searched various forums and tried REPLACE, LEFT/RIGHT etc, but without much success. Finally made three columns, the first with =IF(LEN(A2)=4;REPLACE(A2;3;0;":");"") , the next with IF A2 LEN is 3, then IF A2 LEN is 2. Then filtered out the values and pasted them to a common column. This gave a column with text like “07:05” from which I could get a TIMEVALUE. This feels like a very crude and slow method – ideally I’d like to build one formula to do it all in one fell sweep. Grateful for any help!

Wow, thanks for the fast reply, and great formula! I am afraid that due to the formatting of my post, it appeared that the "time" numbers were in a long sequence in the same cell. However, they are not, I have one value per cell in a column:

705

1440

41

Can you see a way to deal with it to reflect this?

Many thanks,

Michael

Quote from Carim

Hello again,

Fine ... so it is even a lot easier ...

Code

=VALUE(LEFT(TEXT(B2,"0000"),2)&CHAR(58)&RIGHT(TEXT(B2,"0000"),2))

see attached ...

Hope this will help

Display More

Beautiful, thank you so much! Saved me tons of time! Kind regards, Michael