How to separate a string and counting the number of characters in a string minus the spaces-CodePudding

I need help with my code that displays an input box and the user inputs a name then the code splits the names and counts the names displaying the following:

Sub ParseName()
    Dim Name As String
    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)
      Next Cell
    MsgBox Count & " Occurrences of " & Name
    End If
End Sub

CodePudding user response：

Absolutely. You could split the names, but it probably wouldn't help. VBA's split doesn't allow you to split on single characters, AFAIK, as other languages might if you split them a specific delimiter. So you could just loop through the characters using MID to see if each letter is a space or not.

There is a way without any splitting or looping. You can just replace the space and get the length of the what's left.

Len(Replace(Name, " ", ""))

where REPLACE just replaces one string with another, in this case replacing all the spaces with nothing, and LEN just counts the characters in a string.

Here's your code rewritten to use this method, with the unnecessary code and variables removed. I would also change the Name variable, since I believe that is a reserved word in VBA. It will let you do it, but you're potentially impacting some existing behavior. For this particular purpose, using a standalone function to get the character count is someh

Sub ParseName()

    Dim fullName As String, charCount As Integer
    
    fullName = InputBox("Enter First Name, Middle Name, and Last Name")
    If fullName <> "" Then
        charCount = Len(Replace(fullName, " ", ""))
        MsgBox fullName & " has " & charCount & " characters"
        End If
    
End Sub

Bear in mind, however, that there are plenty of other character codes you might not want to count. Tabs, new lines, any of a number of whitespace characters. Non-character symbols. Things of that nature.

Also, this code does not check that the string even contains letters, or that the user input has three names, or that it is in the format First Middle Last.