Fomula: How to convert a TEXT into a usable time which can be formatted

Try:

Select the range CTRL+F to activate Find/Replace command & Replace PM with <space>PM

hit space bar instead of <space> & format the cell as time.

Try this...
=--SUBSTITUTE(SUBSTITUTE(A1,"P"," P"),"a"," A")
Format that cell as time.

Is that something you can work with?

-------------------

That implies that the cell does not contain what we think it does.
Can you attach a workbook with some sample data?

SUBSTITUTE is a case sensitive formula. Replace Ron's formula to;

=--SUBSTITUTE(SUBSTITUTE(LOWER(A1),"p"," P"),"a"," A")

Ooops! Thanks for catching that, Haseeb.