Convert time from text

[Jim]

Hello,

I copied from a pdf several lines of data, in which included the time
formated like this: 12:40P. Two questions:
1) how do I convert this text time to real time accurate to AM/PM?
2) How do I seperate out AM/PM to different cell for other use?

Thanks

 

[Dave Peterson]

I'd select the range to fix and then
Edit|Replace
what: P
with: (space character)PM
replace all

And the same kind of thing if you have 12:40A in cells.

And once you converted that cell to a real time, you wouldn't need another cell.

You could just check to see if the value is before or after noon (.5 of a day).

=if(a1>.5,"PM",if(a1<.5,"AM","Noon"))

I'm not sure how you want to treat 12:00 Noon.

(time is a fraction of a day (12/24 = .5 = half a day)

 

[JBeaucaire]

You only gave one example of a time, so I'm not sure if the date values are
always the same number of characters...how is 1:15AM shown?

Anyway, here's a formula approach to convert the date text in cell C1 to a
real Time value, perhaps in D1:

=IF(ISNUMBER(SEARCH("A",C1)),
TIMEVALUE(LEFT(C1,FIND("A",C1)-1)),
TIMEVALUE(LEFT(C1,FIND("P",C1)-1)))

You can use that same IF test looking for the "A" in the next cell over in E1:

=IF(ISNUMBER(SEARCH("A",C1)),"A","P")

Copy those formulas down to convert the whole column, then copy/paste
special values over the top of themselves to turn them into permanent values.