Converting Poorly Entered Time String into Time Value

[cboyle]

I have a dataset of times entered (incorrectly) in the format "10:32am" that I need to convert to sortable, graph-able time values. The best I've come up with so far is manually breaking the string apart and building it back together as the correct format, but it's not registering as a time value yet.

=LEFT(A1, LEN(A1) - 2) & " " & UPPER(RIGHT(A1, 2))

Any help would be much appreciated! Thanks in advance.

 

[slinky]

The inclusion of the text value is the thing that's stopping you.
You might need to approach it with an if statement to examine whether the last two characters are PM, and add 0.5 to the value if it is.

 

[mikerickson]

Try
=SUBSTITUTE(A1, "a", " a")+0
or
=REPLACE(A1, LEN(A1)-1, 0, " ")+0

The +0 changes the string into a number (time).
The cell then has to be converted to one of the time Number Formats.

 

[slinky]

To add to that, =Left(A1,5) will return the value you desire, BUT it will always report AM.
So...
=IF(UPPER(RIGHT(A1,2)="PM"),LEFT(A1,5)+0.5,LEFT(A1,5))
Format as Time and you're good to go.

 

[cboyle]

@mierickson: First one did the trick, thanks a million