=LEFT on cell with HH:MM format

[Gminnis92]

Good morning,

I have some data, 95% of which is in the correct format, displaying just times in each cell e.g. 09:30. However some cells also contain text, which I do not want. In the majority of cells the text appears after the time, so I thought I could just use an =LEFT formula to keep the time and remove the text after.

The =LEFT formula gives the below results. It works on the cells containing text but then changes the cells, which are currently in the correct format (just showing times), in to numbers and I cannot change this back to the correct format using.


Formula used is =LEFT(A2,5)


=LEFT.JPG


Any help or suggestions on other ways to achieve what I am trying to do would be greatly appreciated.

Many thanks
George

[TMS]

The problem is that using TEXT just gives you a text value ... like it says on the tin.

The examples where you have a true time are just being represented as the text of the original numeric value. So, if you just said =A2 and converted it to General Format, you would get 0.947916667. As it is, you are truncating the value to 0.945. That is, 5 text characters from the original numeric value.

Where you have text in the cell, you get the "time" but it is just TEXT that looks like a time.

Depending on what you want/need to do with the end results will determine the best approach to take.

Try:

=IF(ISNUMBER(A2), A2, TIME(LEFT(A2,2),MID(A2,4,2),0))

[XLent]

another variant that might work for you

B2: =IFERROR(LEFT(TEXT($A2,"hh:mm"),5)+0,$A2)
format as hh:mm; copied down