Excel- How to extracting text from a string of data 2

[wec43wec]

I have a list of data (time values) in one column (approx 2000 rows) that I need the "AM" and/or "PM" text removed. Is there a way to achieve this by using the left, right, or mid function.

A sample of the data is listed as follows:

9:00
9:30 am
8:45
9:00 am

The desired result is as follows:



9:00
9:30
8:45
9:00

 

[SkipVought]

Hi,

Enter the value 1 in an empty cell

COPY that cell

Select your time cells

Edit > Paste Special -- MULTIPLY

Format > Cells - TIME

VOLA, y'all!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[wec43wec]

Skip - that worked !

I usually use this option to convert text to number, but please explain how the "AM" and/or "PM" is removed.

Thanks again

 

[GSCaupling]

wec43wec,

The AM/PM is simply part of the formatting. Dates are just numbers and times are fractions of those numbers. You can get a better explanation from the following than anything I could attempt:

[blue]FAQ68-5827:[/blue].

Good luck!
GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[wec43wec]

Skip / GS - how would you suggest I handle time comparison when some of the individuals are sending me their time in HH:MM (9:30) and other are sending me their time in HH.Units 09.50?

Thanks for any help

 

[SkipVought]

Well it's a mess! The former is in days, while the latter is in hours. You have to standardize your PROCESS!

As long as you can isolate the two, you can convert hours to days in order for everything to be consistent.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}