Trying to split the time and date Text To Columns but getting zeros

Question

I'm trying to split my time and date (numbers) from one column into three columns but am getting zeros when attempting this by treating them as General format.

• My time column: 14:22:13
• My date column: 26/3/2013

When I select delimiter as ':' then click finish I get 00:00:00 as my time over the three columns:

00:00:00 | 00:00:00 | 00:00:00

instead of:

14 | 22 | 13

Answer 1

It seems like cell formatting will not really make this work as you would expect. You might have to resort to using formulas to achieve what you want.

If formulas are acceptable try this