Changing text to time with character delete and add - Excel 2010, Xindows XP 2000

[NAZQAR]

I have a text file I imported into Excel, In column E and F there are times listed as text in this format:
[TABLE="width: 150"]
<tbody>[TR]
[TD]335a
[/TD]
[TD]931a[/TD]
[/TR]
[TR]
[TD]412a[/TD]
[TD]1015a[/TD]
[/TR]
[TR]
[TD]511a[/TD]
[TD]330p[/TD]
[/TR]
[TR]
[TD]525a[/TD]
[TD]416p[/TD]
[/TR]
[TR]
[TD]330p[/TD]
[TD]850p[/TD]
[/TR]
</tbody>[/TABLE]
I want to remove all the "a" and "p" markers but still make sure Excel knows which are AM and which are PM then I want to convert them from text to time values.

I figured out how to do it in several steps but I actually have 3000 lines of data and I had to manually type in the times to make sure they were am or pm. Can this process be automated?

I am comfortable with formulas and VBA so a solution either way would be great.
Your help is greatly appreciated and Thank You in advance.

 

[Momentman]

Here's an idea, the formula converts them to Am and PM and you can change the number format to "h:mm" to show without AM/PM

Excel 2010
	A	B	C	D
1	335a	931a	3:35 AM	9:31 AM
2	412a	1015a	4:12 AM	10:15 AM
3	511a	330p	5:11 AM	3:30 AM
4	525a	416p	5:25 AM	4:16 AM
5	330p	850p	3:30 AM	8:50 AM
Sheet3

Cell Formulas
Range	Formula
C1	=TIME(LEFT(A1,LEN(A1)-3),MID(A1,LEN(A1)-2,2),0)
D1	=TIME(LEFT(B1,LEN(B1)-3),MID(B1,LEN(B1)-2,2),0)

 

[NAZQAR]

Thank you very much for your help, but hat solution does not quite work, it does not seem to always know which ones should be AM and which PM. If you look at your example in D5 it converted 850p to 8:50 AM and that should be a PM. tha "a" and "p" marker are the for AM and PM in the text file I imported.

 

[MickG]

Try this:-
Code assumes your data (times) start in "E1 & F1".

[COLOR="Navy"]Sub[/COLOR] MG02Sep37
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] AP [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("E1"), Range("E" & Rows.Count).End(xlUp)).Resize(, 2)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    AP = IIf(Right(Dn.Value, 1) = "a", "AM", "PM")
    Txt = IIf(Len(Dn.Value) = 5, Left(Dn.Value, 2) & ":" & Mid(Dn.Value, 3, 2), Left(Dn.Value, 1) & ":" & Mid(Dn.Value, 2, 2))
    Dn.Value = Txt & " " & AP
    Txt = "": AP = ""
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Momentman]

Thank you very much for your help, but hat solution does not quite work, it does not seem to always know which ones should be AM and which PM. If you look at your example in D5 it converted 850p to 8:50 AM and that should be a PM. tha "a" and "p" marker are the for AM and PM in the text file I imported.

Ooooh, didn't notice that

Should be

Excel 2010
	A	B	C	D
1	335a	931a	3:35 AM	9:31 AM
2	412a	1015a	4:12 AM	10:15 AM
3	511a	330p	5:11 AM	3:30 PM
4	525a	416p	5:25 AM	4:16 PM
5	330p	850p	3:30 PM	8:50 PM
MASTER

Cell Formulas
Range	Formula
C1	=IF(RIGHT(A1,1)="a",TIME(LEFT(A1,LEN(A1)-3),MID(A1,LEN(A1)-2,2),0),TIME(12+LEFT(A1,LEN(A1)-3),MID(A1,LEN(A1)-2,2),0))
D1	=IF(RIGHT(B1,1)="a",TIME(LEFT(B1,LEN(B1)-3),MID(B1,LEN(B1)-2,2),0),TIME(12+LEFT(B1,LEN(B1)-3),MID(B1,LEN(B1)-2,2),0))

 

[NAZQAR]

Excellent. Both solutions work. I appreciate your help gentlemen (Or ladies if you are????, although momentman, and mickg do not seem like ladies user names, but I ramble, thanks.)

 

[MickG]

You're welcome