Get Dates from String and separate in to columns - Formula or Macro please

[JosephGourvenec]

Hi Mr and Mrs Excel'er,

First time poster here and would love your help

I've a string coming from a database e.g. "1000000000 – 23092014 (23092014)"

The first part is an ID separated by " - " then a date "23092014" with another date in brackets "(23092014)".

Ideally I'd like to copy that cell and split the information in to 3 columns and convert the date string to UK date format dd/mm/yyyy

E.g.
Col K: 1000000000
Col L: 23/09/2014
Col M: 23/09/2014

A formula would be better for me as VB and Macros I'm not very good with but if needs must and all that.

Thank you for your help in advance

Cheers,
Joseph - MrExcel youngling "Star War themed "

 

[MaxSmart]

Try this out;

I have made the following assumptions
1) After the dash in text the format of the data is the same – i.e. always DDMMYYY followed by one space followed by (DDMMYYY)
2) The imported data is in column A – if not the change the A ref in the formulae to whatever the correct column is
The first thing we need to do is find the character position of the first space in the text. Create the following formula in an unused column. I have used B in my example, if yours is different then change all references to B to whatever it should be;

So in column B, I have the following formula;
=FIND(" ",A1,1)
In column K the formula would be;
=MID(A1,1,B1-1)
In column L the formula would be;
=DATEVALUE(MID(A1,B1+3,2)&"/"&MID(A1,B1+5,2)&"/"&MID(A1,B1+7,4))
In column M the formula would be;
=DATEVALUE(MID(A1,B1+13,2)&"/"&MID(A1,B1+15,2)&"/"&MID(A1,B1+17,4))
Columns L and M should be formatted as whatever date format you prefer

Let me know if this solves your problem….

 

[FormR]

Hi Joseph,

Here are some alternative formulas that you could also try:

Excel 2012
	J	K	L	M
2	1000000000 23092014 (23092014)	1000000000	23/09/2014	23/09/2014
Sheet1

Cell Formulas
Range	Formula
K2	=LEFT(J2,FIND("–",J2)-1)
L2	=TEXT(MID(J2,FIND("–",J2)+2,8),"00-00-0000")+0
M2	=TEXT(LEFT(RIGHT(J2,9),8),"00-00-0000")+0

 

[MaxSmart]

Hi FormR

Many ways to skin a cat - yours is a bit more elegant

I included the DATEVALUE so that it would be a true date field and could be sorted on that basis. Joseph can still get the desired text output by selecting the appropriate date format.

Regards.........

 

[FormR]

Many ways to skin a cat

Hi Max, couldn't' agree more with that statement

I included the DATEVALUE so that it would be a true date field

FYI: My suggestion also returns a true date serial number, it's the "+0" that coerces the text value to a true date.

 

[MaxSmart]

FYI: My suggestion also returns a true date serial number, it's the "+0" that coerces the text value to a true date.

Well there you go - I did not realise that adding a zero converted it to a date serial number - I learnt something - thankyou

 

[Rick Rothstein]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M2[/TH]
[TD="align: left"]=TEXT(LEFT(RIGHT(J2,9),8),"00-00-0000")+0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I think you can shorten the above formula in M2 to this...

=TEXT(-RIGHT(A1,10),"00-00-0000")+0

 

[JosephGourvenec]

Wow thank you everyone for helping with this one and going to try them now.

Talk soon

Cheers,
Joe