VBA for Extracting a Date from a Cell with Other Text

[FrenchCelt]

Hello,

I'm coding a macro that formats a table of data copied and pasted from an email, and part of what I need is to extract a date from a cell with other text, which I don't need, so all I have left is the date itself. The cell involved will always be in A1. Here is an example:

NHO - Friday 3/4/22 -7am-4pm

I simply want to be left with 3/4/2022.

Does anyone have any suggestions?

 

[mumps]

Try:

Sub ExtractDate()
    Range("A1") = Trim(Split(Range("A1"), "-")(1))
End Sub

 

[FrenchCelt]

I end up with this afterward:

Friday 3/4/22

That's a good start. Can you eliminate the day of the week as well? It won't always be Friday, so it shouldn't be that specific.

 

[EFANYoutube]

Hey FrenchCelt
This should do it no matter what data is either side of the date with the exceptions of "/"

Function Get_Date() As Date
    Dim SpaceBefore As Integer
    Dim SpaceAfter As Integer
    With ThisWorkbook.Sheets("Data").Range("A1")
        SpaceBefore = InStr(1, .Value, "/", vbTextCompare) - 2
        SpaceAfter = InStr(SpaceBefore + 2, .Value, " ", vbTextCompare)
        Get_Date = DateValue(Mid(.Value, SpaceBefore, SpaceAfter - SpaceBefore))
    End With
End Function

Sub Processing()
ThisWorkbook.Sheets("Data").Range("A1") = Get_Date

End Sub

 

[mumps]

Try:

Sub ExtractDate()
    Range("A1") = Split(Trim(Split(Range("A1"), "-")(1)), " ")(1)
End Sub

 

[FrenchCelt]

Thanks mumps, that did the trick.

I appreciate your suggestion, EFANYoutube. I wasn't able to get it to work by the time mumps got back to me with the reply that did work.

 

[mumps]

You are very welcome.

 

[EFANYoutube]

No issues, Just make sure that the format is always exactly the same as it is in your example or Mumps will not work. If you want further help with mine let me know, it did work in my mock workbook