Insert Space in Time of Day

[RJQMAN]

I import a large amount of data from another source - the data
sometimes has the time of day displayed as (for example) 2:10PM and
sometimes as 2:10 PM with a space before the PM. I would like to be
able to sort the data by time of day, but because the two formats are
different, I do not know how to do it. Ideally I would like to insert
a space before the PM so that I can use a vacant column and convert
the time of day to a number (0.590277778) and then sort by the number
so that the times are consecutive, but if the data does not have a
space, I guess Excel does not know it is a time of day. Is there any
easy way to convert the data to add the space so that Excel recognizes
it as a time of day? A macro would be fine or any suggestion would be
very much appreciated.

Thanks in advance from a person who knows just enough about Excel to
screw it up...

 

[Dave Peterson]

Maybe you could just select the range and do:
Edit|Replace
what: PM
with: (spacebar)PM
replace all

 

[Rick Rothstein \(MVP - VB\)]

If there are too many cells to easily create the selection, then I think the
OP could Edit|Replace (space)PM with just PM for the entire workbook first
and then follow that with Edit|Replace PM with (space)PM. That should get
all PMs to have a single space in front of it. I guess the procedure would
then have to be repeated for AM also.

Rick

 

[Ron Rosenfeld]

If there are too many cells to easily create the selection, then I think the
OP could Edit|Replace (space)PM with just PM for the entire workbook first
and then follow that with Edit|Replace PM with (space)PM. That should get
all PMs to have a single space in front of it. I guess the procedure would
then have to be repeated for AM also.

Rick

I believe that if the purpose is to get Excel to recognize the string as time,
it doesn't matter if there are one or a few spaces before the PM.
--ron

 

[Rick Rothstein \(MVP - VB\)]

If there are too many cells to easily create the selection, then I think

I believe that if the purpose is to get Excel to recognize the string as
time,
it doesn't matter if there are one or a few spaces before the PM.

Well, this goes to show you how observant I am... Excel does accept time
value entries where the there is more than one space separating the time
from the AM/PM indicator... as a matter-of-fact, it will remove all excess
spaces for you.

Rick