Inserting a colon into text

[muskrat1977]

Hello

I have imported text that has the following format:
20 May 2014 1715

I need it to be in the following format:
20 May 2014 17:15

Can you help

 

[Claus Busch]

Hi,

Am Tue, 18 Feb 2014 16:59:27 +0000 schrieb muskrat1977:

I have imported text that has the following format:
20 May 2014 1715

your text in A1. Then in B1:
=LEFT(A1,LEN(A1)-5)+TIME(MID(A1,LEN(A1)-3,2),RIGHT(A1,2),)
and format the cell DD MMM YYYY hh:mm



Regards
Claus B.

 

[Claus Busch]

Hi,

Am Tue, 18 Feb 2014 19:02:12 +0100 schrieb Claus Busch:

=LEFT(A1,LEN(A1)-5)+TIME(MID(A1,LEN(A1)-3,2),RIGHT(A1,2),)
and format the cell DD MMM YYYY hh:mm

or try:
=--SUBSTITUTE(A1,MID(A1,LEN(A1)-2,1),MID(A1,LEN(A1)-2,1)&":")
and format custom DD MMM YYYY hh:mm


Regards
Claus B.