VBA Code: How to parse out each First, Middle, and Last names from an input function and then using-CodePudding

I am very confused on where I am at right now. But I need to parse/separate out the first, middle, and last name of a input function. Then display the number of characters that is in that entire name minus the spaces as well as display the number of characters in users first and last names minus the spaces.

ex: Matt Lose Wright has 15 Characters Matt Wright has 10 Characters

I am trying

My Code:

Sub ParseName()
    Dim name As String
    name = InputBox("Enter First Name, Middle Name, and Last Name")
    
    Dim First As Double
    Dim Middle As Double
    Dim Last As Double
    
    First = InStr(1, name, "First", vbTextCompare)
    Middle = InStr(2, name, "Middle", vbTextCompare)
    Last = InStr(3, name, "Last", vbTextCompare)
    
    Dim Count As Integer
    Dim Cell As Object
    Dim n As Integer
    Count = 0
    name = InputBox("Enter First Name, Middle Name, and Last Name")
    If name = "" Then
        For Each Cell In Selection
         n = InStr(1, Cell.Value, name)
         While n <> 0
            Count = Count   1
            n = InStr(n   1, Cell.Value, name)
         Wend
      Next Cell
    MsgBox Count & " Occurrences of " & name
    End If
End Sub

CodePudding user response：

To summarise - I have shown you three techniques.

A = "Matt Lose Wright"
B = Split(A, " ")
MsgBox Len(Replace(A, " ", ""))
For Each name in B
    MsgBox Name & " " & Len(Name)
Next
MsgBox B(0) & " " & B(UBound(B)) & " " & Len(B(0)) & " " & Len(B(UBound(B)))

Output

14
Matt 4
Lose 4
Wright 6
Matt Wright 4 6

See Split, your friend when parsing - https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function

A = "Matt Lose Win Wright"
B = Split(A, " ")
MsgBox Len(Replace(A, " ", ""))
For Each name in B
    MsgBox Name & " " & Len(Name)
Next
MsgBox B(0) & " " & B(UBound(B)) & " " & Len(B(0)) & " " & Len(B(UBound(B)))

Output

17
Matt 4
Lose 4
Win 3
Wright 6
Matt Wright 4 6