# Splitting a string up and making a DATE

#### GetHitFooL

##### New Member
Good Morning Excel Gods,

I am looking for some help pulling some information from a string I neeed.

For Example,

Yellow 2015 I09 (4.29)

I need to become just a date that is 4/29/2015

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Mackers

##### Well-known Member
Hi GetHitFool

It all depends on whether the string is in the same format each time. In the example you give, you can split it as follows (if the string is in B2):

Get the year: --MID(B2, FIND(" ", B2)+1, 4)
Get the month: --MID(B2, FIND("(", B2)+1, FIND(".", B2, FIND("(", B2)+1)-FIND("(", B2))
Get the day: --MID(B2, FIND(".", B2)+1, FIND(")", B2)-FIND(".", B2)-1)

Stick that in the DATE(Year, Month, Day) so:

=DATE(--MID(B2, FIND(" ", B2)+1, 4), --MID(B2, FIND("(", B2)+1, FIND(".", B2, FIND("(", B2)+1)-FIND("(", B2)), --MID(B2, FIND(".", B2)+1, FIND(")", B2)-FIND(".", B2)-1))

If your formatting changes you will have to adjust - in this case I have assumed the second word is the year, there is only one full stop and one "(" and one ")" in your string and that it always goes month.day within the brackets.

Edit: I am from the UK so there may be a difference in terms of formatting for formulas, I'm not sure but exercise caution

Hope that helps

Mackers

#### GetHitFooL

##### New Member
So it works, how can I code this in VB to run that?

Hi GetHitFool

It all depends on whether the string is in the same format each time. In the example you give, you can split it as follows (if the string is in B2):

Get the year: --MID(B2, FIND(" ", B2)+1, 4)
Get the month: --MID(B2, FIND("(", B2)+1, FIND(".", B2, FIND("(", B2)+1)-FIND("(", B2))
Get the day: --MID(B2, FIND(".", B2)+1, FIND(")", B2)-FIND(".", B2)-1)

Stick that in the DATE(Year, Month, Day) so:

=DATE(--MID(B2, FIND(" ", B2)+1, 4), --MID(B2, FIND("(", B2)+1, FIND(".", B2, FIND("(", B2)+1)-FIND("(", B2)), --MID(B2, FIND(".", B2)+1, FIND(")", B2)-FIND(".", B2)-1))

If your formatting changes you will have to adjust - in this case I have assumed the second word is the year, there is only one full stop and one "(" and one ")" in your string and that it always goes month.day within the brackets.

Edit: I am from the UK so there may be a difference in terms of formatting for formulas, I'm not sure but exercise caution

Hope that helps

Mackers

#### GetHitFooL

##### New Member
So I did this:

Range("P2").Formula = "=DATE(--MID(G2, FIND(" ", G2)+1, 4), --MID(G2, FIND("(", G2)+1, FIND(".", G2, FIND("(", G2)+1)-FIND("(", G2)), --MID(G2, FIND(".", G2)+1, FIND(")", G2)-FIND(".", G2)-1))"

And its throwing an error Saying Expected: End of Statement and highlights the middle of the code.

If I go into the actual spreadsheet and just past that it works fine?

#### Mackers

##### Well-known Member
I don't know VBA, sorry - hopefully one of the more experienced folks around here can help you out.

Good luck!

Replies
14
Views
2K
Replies
9
Views
315
Replies
3
Views
435
Replies
8
Views
674
Replies
6
Views
278

1,195,617
Messages
6,010,729
Members
441,565
Latest member
menangterus556

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back