How to extract data from a concatenated string in excel macro

Question

Am new to this - trying to pull out a name and date from a concatenated text string - each line has a name with the month - all different and the month must be changed for each search.
[Attached is screenshot of my results using MID that extracts date][1]

I have attempted MID and RIGHT using first uppercase to the left of the comma but have had no luck. Any help would be appreciated.

I do have a script that process the data - the following is the VBA code for pulling name and date: Name ActiveCell.Formula = "=IFERROR(MID(C3,1,FIND(""January"",C3,1)-1),IFERROR(MID(C3,1,FIND(""February"",C3,1)-1),IFERROR(MID(C3,1,FIND(""March"",C3,1)-1),IFERROR(MID(C3,1,FIND(""April"",C3,1)-1),IFERROR(MID(C3,1,FIND(""May"",C3,1)-1),IFERROR(MID(C3,1,FIND(""June"",C3,1)-1),IFERROR(MID(C3,1,FIND(""July"",C3,1)-1),IFERROR(MID(C3,1,FIND(""August"",C3,1)-1),IFERROR(MID(C3,1,FIND(""September"",C3,1)-1),IFERROR(MID(C3,1,FIND(""October"",C3,1)-1),IFERROR(MID(C3,1,FIND(""November"",C3,1)-1),IFERROR(MID(C3,1,FIND(""December"",C3,1)-1),""No matches""))))))))))))"

Date ActiveCell.Formula = "=IFERROR(MID(C3,FIND(""January"",C3)*1,20),IFERROR(MID(C3,FIND(""February"",C3)*1,20),IFERROR(MID(C3,FIND(""March"",C3)*1,20),IFERROR(MID(C3,FIND(""April"",C3)*1,20),IFERROR(MID(C3,FIND(""May"",C3)*1,20),IFERROR(MID(C3,FIND(""June"",C3)*1,20),IFERROR(MID(C3,FIND(""July"",C3)*1,20),IFERROR(MID(C3,FIND(""August"",C3)*1,20),IFERROR(MID(C3,FIND(""September"",C3)*1,20),IFERROR(MID(C3,FIND(""October"",C3)*1,20),IFERROR(MID(C3,FIND(""November"",C3)*1,20),IFERROR(MID(C3,FIND(""December"",C3)*1,20),""nope""))))))))))))"

I'm sure there is an easier way - I just haven't figured it out and I'm being pressured to get this data out -

![1]: https://i.stack.imgur.com/dT92Q.jpg

Very easy with VBA..............is this option acceptable to you?? — Gary's Student
Yes - I run VBA script now using the same methodology with, unfortunately, the same results — Shellback

Gary's Student Gary's Student · Accepted Answer · 2018-05-14T17:31:43

Here are two User Defined Functions:

Public Function DateGrabber(s As String) As Date
    Dim temp As String, temp2 As String, i As Long, L As Long
    temp = s
    temp2 = ""
    L = Len(s)
    For i = L To 1 Step -1
        temp2 = Mid(temp, i, 1) & temp2

        If Mid(temp, i, 1) Like "[A-Z]" Then
            DateGrabber = CDate(temp2)
            Exit Function
        End If
    Next i
End Function


Public Function DateDiscarder(s As String) As String
    Dim i As Long, L As Long
    L = Len(s)
    For i = L To 1 Step -1
        If Mid(s, i, 1) Like "[A-Z]" Then
            DateDiscarder = Left(s, i - 1)
            Exit Function
        End If
    Next i
End Function

This first function walks backwards from the end of the string until it finds the first capital letter. That is how it finds the date part.

The second UDF just discards the date using the same criteria:

How to extract data from a concatenated string in excel macro

1 Answers