Convert Text numbers to time format

I have numbers (time) imported as text so I cannot simply format the cell to time.

does anybody know a formula that will convert this to the following

130702 - to - 01:07:02 PM
093506 - to - 09:03:06 AM
101607 - to - 10:16:07 AM
123958 - to - 12:39:58 AM
225409 - to - 10:54:09 PM

=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

and copy the formula down, then format all cells as hh:mm:ss AM/PM

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"jermsalerms" <[email protected]>
wrote in message
news:[email protected]...
>
> I have numbers (time) imported as text so I cannot simply format the
> cell to time.
>
> does anybody know a formula that will convert this to the following
>
> 130702 - to - 01:07:02 PM
> 093506 - to - 09:03:06 AM
> 101607 - to - 10:16:07 AM
> 123958 - to - 12:39:58 AM
> 225409 - to - 10:54:09 PM
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile:
http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=503133
>

Try this formula

=TEXT(A1,"00\:00\:00")+0

I tried Bob's 1st and it worked so Im sticking with it. I sure the other one works well too