I receive a report that provides me a time of say 10:00:00AM. I need this
into a format that Excel recognizes as a time. If I change the format of the
cell to HH:MM:SS AM/PM it doesn't recognize it I'm assuming because the lack
of a space between the actual time and the AM/PM. Is there a way to add the
spaces to all of these sections or to get Excel to recognize this as a time?
Otherwise, I'm hitting the space bar more times than it is worth.

Thanks,
Kyle

You could use Edit/ Replace and change "AM" to " AM"

Another option is a formula such as =--(LEFT(A2,LEN(A2)-2)&" "&RIGHT(A2,2))
and format the result as time.
--
David Biddulph

"Kyle" wrote in message
...
I receive a report that provides me a time of say 10:00:00AM. I need this
into a format that Excel recognizes as a time. If I change the format of
the
cell to HH:MM:SS AM/PM it doesn't recognize it I'm assuming because the
lack
of a space between the actual time and the AM/PM. Is there a way to add
the
spaces to all of these sections or to get Excel to recognize this as a
time?
Otherwise, I'm hitting the space bar more times than it is worth.

Thanks,
Kyle

Try:

=VALUE(LEFT(A1,8)&" "&RIGHT(A1,2))

OR

=--(LEFT(A1,8)&" "&RIGHT(A1,2))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Kyle" wrote in message
...
I receive a report that provides me a time of say 10:00:00AM. I need this
into a format that Excel recognizes as a time. If I change the format of
the
cell to HH:MM:SS AM/PM it doesn't recognize it I'm assuming because the lack
of a space between the actual time and the AM/PM. Is there a way to add the
spaces to all of these sections or to get Excel to recognize this as a time?
Otherwise, I'm hitting the space bar more times than it is worth.

Thanks,
Kyle

Edit replace. To easy. Thank you very much.

"David Biddulph" wrote:

You could use Edit/ Replace and change "AM" to " AM"

Another option is a formula such as =--(LEFT(A2,LEN(A2)-2)&" "&RIGHT(A2,2))
and format the result as time.
--
David Biddulph

"Kyle" wrote in message
...
I receive a report that provides me a time of say 10:00:00AM. I need this
into a format that Excel recognizes as a time. If I change the format of
the
cell to HH:MM:SS AM/PM it doesn't recognize it I'm assuming because the
lack
of a space between the actual time and the AM/PM. Is there a way to add
the
spaces to all of these sections or to get Excel to recognize this as a
time?
Otherwise, I'm hitting the space bar more times than it is worth.

Thanks,
Kyle