convert text to real time value

[President]

hello

i have data in my cells like this

11Pm-2AM

2AM-5AM

9PM-12AM i want this to be recognized as recognizable time in excel i want this to be converted like this 11:00 PM in A2 and 2:00 AM in B2

<tbody>
</tbody>

 

[gaz_chops]

Is it always just hours? Or could it be Hrs & Mins 11.30pm-2am

 

[President]

hours only

 

[Special-K99]

With data in cell A1

From time
=TIME(SUBSTITUTE(SUBSTITUTE(UPPER(LEFT(A1,FIND("-",A1)-1)),"AM",""),"PM","")+IF(RIGHT(LEFT(A1,FIND("-",A1)-1),2)="PM",1,0)*12,0,0)

To Time
=TIME(SUBSTITUTE(SUBSTITUTE(UPPER(RIGHT(A1,LEN(A1)-FIND("-",A1))),"AM",""),"PM","")+IF(RIGHT(RIGHT(A1,LEN(A1)-FIND("-",A1)),2)="PM",1,0)*12,0,0)


UPDATE: 12AM in the last example returns 12PM
00AM returns 12AM

 

[FormR]

hours only

Another possible option (the cells containing the formula would need to be formatted as time).

Excel 2012
	A	B	C
1	11PM-2AM	23:00:00	02:00:00
2	2AM-5AM	02:00:00	05:00:00
3	9PM-12AM	21:00:00	00:00:00
Sheet1

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(SUBSTITUTE(UPPER(LEFT($A1,FIND("-",$A1)-1)),"PM",":00 PM"),"AM",":00 AM")+0
C1	=SUBSTITUTE(SUBSTITUTE(UPPER(MID($A1,FIND("-",$A1)+1,99)),"PM",":00 PM"),"AM",":00 AM")+0

 

[President]

Special KK99
thank you very much it worked.

wow, i did not expect it would require this sophistication

thanks FormR
your formula also worked.

i saved them in my favrourate.