Rick,

My original: 20070917 at: 1900
Results with your formula: 9/17/07 at 1:00

I would like to remove the 'at' as well, the time is not displaying
correctly. I would like the time to output 19:00. I'm not sure where the
1:00. Could i ask also that you explain your formula a bit. I'm having some
difficulties understanding how it reads. Thanks for all your help.


"Rick Rothstein (MVP - VB)" wrote:

You can shorten your formula and save a couple of function calls by using
virtually the same string functions this way instead...

=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)),"m/d/yy")&" at
"&MID(A1,13,2)&":"&RIGHT(A1,2)

However, this requires a separate cell to implement and I kind of get the
impression the OP wants to do the text parsing within the same cell (sort
of like a "custom format"). That, of course, would require a macro
solution.

Of course it would help if I had pasted my formula into my message instead
of your formula. This is formula I meant to include in my post...

=--MID(A1,5,2)&"/"&--MID(A1,7,2)&"/"&MID(A1,3,2)&MID(A1,9,6)&":"&RIGHT(A1,2)

Rick