ExcelBanter - Inserting a colon into text

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Inserting a colon into text (https://www.excelbanter.com/excel-discussion-misc-queries/449830-inserting-colon-into-text.html)

muskrat1977

Inserting a colon into text

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

Inserting a colon into text

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus Busch

Inserting a colon into text

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

All times are GMT +1. The time now is 07:58 AM.