Splitting a string up and making a DATE

[GetHitFooL]

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

 

[Mackers]

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]

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]

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]

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

Good luck!