TimeValue(Substitute)

[nance1432m]

Hi, I am working with a spreadsheet that was pulled from an external data source. It imported the time column as text (example: 07:30m or 12:00am). No space between. I've gotten it to work if I break it into 2 separate columns using the following 2 formulas: =TIMEVALUE(SUBSTITUTE(D2,"pm"," pm")) and =TIMEVALUE(SUBSTITUTE(D2,"am"," am")). Can anyone help so that it's one formula in one column? Please

 

[Fluff]

Hi & welcome to MrExcel.
How about
=REPLACE(A2,6,," ")+0

and then format as time

 

[Joe4]

Welcome to the Board!

Try:

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(D2,"pm"," pm"),"am"," am"))

 

[nance1432m]

Thank you!!! Worked perfectly! You have saved me hours of frustration!

 

[Fluff]

Not sure which of us you are referring too, but glad we could help & thanks for the feedback