=LEFT on cell with HH:MM format

**Gminnis92** · 06-17-2020, 07:34 AM

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** · 06-17-2020, 07:49 AM

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:

Formula:

Please Login or Register  to view this content.

**XLent** · 06-17-2020, 08:24 AM

another variant that might work for you

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

=LEFT on cell with HH:MM format

LinkBack

Thread Tools

Rate This Thread

Display

=LEFT on cell with HH:MM format

Re: =LEFT on cell with HH:MM format

Re: =LEFT on cell with HH:MM format

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Format Cell if it has been a week since Date in Left Adjacent Cell

Formula to move data to Left when LEFT cell is blank?

Formula to move data to Left when LEFT cell is blank?

Long date format won't fit in one cell if left justified

[SOLVED] Format cell with a value with a color, then range of cells to left a different color

LEFT formula, cell format as number?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions