VBA If statement containing a word & extracting characters

[aurelien_21]

Cell A2 in my active sheet has the value "Class Data, Day 1 - Monday 6th, June 2022"

I need a VBA If statement which determines whether cell A2 (in

Please Login or Register  to view this content.

format) contains the word "Day", and if it does, Then my String variable "Day" becomes "Monday 6th, June 2022" (so preferably everything after the "- ")

I have absolutely no idea how to write this in code. My best guess would've been along the lines of:

Please Login or Register  to view this content.

Thanks so much for any help you can offer!

[cytop]

I was going to reply in your other thread about this point with an observation ...

Potentially every cell with a date will contain 'day' (Monday, Tuesday... etc) so you could possibly search for '(Space)day' ... but you don't give enough information to say for certain.

Another possibility, if the layout is consistent and cells you are interested in contains a separator as you have shown (" - ")

Please Login or Register  to view this content.

An aside ...

... Then my String variable "Day" becomes ...

Don't name variables using VBA Function names ...

[jindon]

Something like this
Use of "Day" as a variable name is not recommended since vba has Day function.

Please Login or Register  to view this content.

[aurelien_21]

I was going to reply in your other thread about this point with an observation ...

Potentially every cell with a date will contain 'day' (Monday, Tuesday... etc) so you could possibly search for '(Space)day' ... but you don't give enough information to say for certain.

Another possibility, if the layout is consistent and cells you are interested in contains a separator as you have shown (" - ")

Please Login or Register  to view this content.

An aside ...


Don't name variables using VBA Function names ...

Yes, sorry I should've attached a sample of the sheet. See attached.
RE: naming variables using VBA Function names. Good tip thanks, I'll change it to "Survey_Date"

[cytop]

The workbook adds nothing as there is no code to provide context for what you want. The code in posts #2 & #3 will do as you want - possibly with a change or 2. You just need to fit it in somewhere

[aurelien_21]

Something like this
Use of "Day" as a variable name is not recommended since vba has Day function.

Please Login or Register  to view this content.

Wasn't working with the $ but without, this works great. Thanks for your help!

[aurelien_21]

The workbook adds nothing as there is no code to provide context for what you want. The code in posts #2 & #3 will do as you want - possibly with a change or 2. You just need to fit it in somewhere

Yep, got it all working. Thanks for your help and advice

[jindon]

Wasn't working with the $ but without, this works great. Thanks for your help!

Then your excel is something wrong.