MID function not working

[mswaf01]

I need to cut a long column of cells (millions) quickly. The current data displayed in the cell its date and time, displayed in a custom format of dd/mm/yy hh:mm

I need to quickly cut this in a new column that only contains the hour. However, the MID function isn't working for this:

. I have also tried this using MID and LENS together.

I think the problem is that the DATETIME is in a custom format rather than general, but when I convert to general it reverts to a series of seemingly unrelated numbers, e.g.:

What do I need to do to be able to extract only the hour data?

 

[Alex Blakenburg]

Glad you have something that works for you.
If you want to post a more realistic example of what the data looks like when you import the data and what result you are wanting (preferably using the XL2BB tool) it is likely that we can come up with a cleaner option but we will leave it with you.