How do I convert text into time data?

Question

I have text in one column that has times formatted as "1230 pm" and need to convert it to time.

I managed to convert the "am" data but didn't get it to work with the "pm" data.

Answer 1

If the text is in A1, then you can format a cell for the time format you want, and use this formula:

=TIMEVALUE(LEFT(A1,LEN(A1)-5) & ":" & MID(A1,LEN(A1)-4,2) & " " & RIGHT(A1,2))

Make sure you format the cell as time format otherwise you'll get a funky decimal. Also, this only works if the space is between the number and the am/pm.