VBA - Extract Second word from Cell and Extract word before ","

[eli_m]

Hi,

I am currently using:
Cells(ActiveCell.Row, "F").Value
Which is "John Doe" but I only want it to display the second word "Doe"

I also have

Cells(ActiveCell.Row, "A").Value
Which is "SMITH RODGERS, Jane" but I only want it to display the words that appear before the "," so it displays "SMITH RODGERS"

The full code is:

Option Explicit
Sub CalendarInvite()
    Dim OutApp As Outlook.Application, Outmeet As Outlook.AppointmentItem

        Set OutApp = Outlook.Application
        Set Outmeet = OutApp.CreateItem(olAppointmentItem)
        
        With Outmeet
            .MeetingStatus = olMeeting
            .ReminderMinutesBeforeStart = 15
            .Subject = "Support: " & Cells(ActiveCell.Row, "F").Value & " Client: " & Cells(ActiveCell.Row, "A").Value & " - BOOKED"
            .Body = Cells(ActiveCell.Row, "J").Value
            .Location = Cells(ActiveCell.Row, "H").Value
            .Start = Cells(ActiveCell.Row, "B").Value
            .AllDayEvent = True
            .Display
        End With

    Set OutApp = Nothing
    Set Outmeet = Nothing
End Sub

Thanks in advance

 

[Peter_SSs]

From your description & then looking at your code I imagine that it is just the "Subject" line that you are talking about. If so, try this for that line

.Subject = "Support: " & Split(Cells(ActiveCell.Row, "F").Value)(1) & " Client: " & Split(Cells(ActiveCell.Row, "A").Value, ",")(0) & " - BOOKED"

 

[eli_m]

From your description & then looking at your code I imagine that it is just the "Subject" line that you are talking about. If so, try this for that line

.Subject = "Support: " & Split(Cells(ActiveCell.Row, "F").Value)(1) & " Client: " & Split(Cells(ActiveCell.Row, "A").Value, ",")(0) & " - BOOKED"

As soon as I got the email that "Peter_SSs replied" I knew it was going to be the right answer!
Thank you so much

 

[Peter_SSs]

You're welcome. Glad it is what you wanted.