How to convert text into time format?

[adelkam]

I have excel data file that contains time inserted as text in inconsistent format.

I need a formula that format the any of these time into text in the form: 00:00 AM/PM

sample of the data that I see:

12am "two digits no space between number and am"
7 pm "1 digit with space between number and pm"
8:00pm "no space"

etc.

I need a formula that reads and exracts this type of data and clean it up as "00:00 AM/PM"

Thanks

 

[pgc01]

Hi

Try:

=0+SUBSTITUTE(SUBSTITUTE(A1,"a"," a"),"p"," p")

and format the cell as time.

 

[shg]

Also posted at http://www.excelforum.com/excel-worksheet-functions/845202-how-to-convert-text-into-time-format.html