CODEWITHSUNDEEP
vbaexcel
Jeevan
Jeevan

Reputation: 477

Count the number of empty spaces in front and back of the string

I am reading a file line by line in Excel VBA.

I have some strings for example, 

"             ooo"
"         ooo                   "

I want to find the number of empty spaces in the front of the string. If I use Trim, it is removing empty spaces from both back and front of the string.

Upvotes: 1

Views: 1838

Answers (4)

Vegard
Vegard

Reputation: 4927

Function test(s As String) As Integer
    Dim str As String
    str = "[abcdefghijklmnopqrstuvwxyz0123456789]"

    Dim spaceCounter As Integer

    For i = 1 To Len(s)
        If Not Mid(s, i, 1) Like str Then
            spaceCounter = spaceCounter + 1
        Else
            Exit For
        End If
    Next i

    test = spaceCounter
End Function

By popular request: Why use this function instead of Trim, LTrim, etc?

Well, to summarize the full explanation, not all spaces can be removed with Trim. But they will be removed with this function.

Consider this example (I'll borrow PhilS' solution for illustrative purposes):

Sub testSpaceRemoval()
    Dim str1 As String
    str1 = " " & Chr(32) & Chr(160) & "a"
    Debug.Print Chr(34) & str1 & Chr(34)
    Debug.Print NumberOfLeadingSpaces(str1)
    Debug.Print test(str1)
End Sub

Result:

"   a"  
2   
3

Here we can see that the string clearly contains 3 spaces, but the solution using LTrim only counted 2.

So, what to use?

Well, it depends. If you have a dataset where you know you won't get non-breaking characters, use Trim as much as you want! If you think you can get non-breaking characters, Trim alone will not be enough.

Characters to look out for are, quoted from the explanation linked above:

leading, trailing, or multiple embedded space characters (Unicode character set values 32 and 160), or non-printing characters (Unicode character set values 0 to 31, 127, 129, 141, 143, 144, and 157)

Trim can remove chr(32) (as demonstrated above) but not chr(160), because 32 is the regular space and 160 is a non-breaking space.

If you're a stickler for covering your behind, consider this total solution:

Function cleanSpecialCharacters(str As String) As String
    bannedChars = Chr(127) & "," & Chr(129) & "," & Chr(141) & "," & Chr(143) & "," & Chr(144) & "," & Chr(157) & "," & Chr(160)

    str = Application.WorksheetFunction.Clean(str)
    str = Application.WorksheetFunction.Trim(str)

    For Each c In Split(bannedChars, ",")
        str = Replace(str, c, "")
    Next

    cleanSpecialCharacters = str
End Function

For OP's particular question, it would have to be a little more tailored.

Upvotes: 3

user3598756
user3598756

Reputation: 29421

Sub main()
    Dim strng As String
    Dim i As Long

    strng = "         ooo                   "

    i = 1
    Do While Mid(strng, i, 1) = " "
        i = i + 1
    Loop
    MsgBox "number of front empty spaces: " & i - 1
End Sub

or use LTrim function:

Sub main2()
    Dim strng As String
    strng = "         ooo                   "
    MsgBox "number of front empty spaces: " & Len(strng) - Len(LTrim(strng))
End Sub

Upvotes: -1

PhilS
PhilS

Reputation: 1661

You could use the LTrim and RTrim functions. - I would assume that is faster, than looping through the string and doing character comparisons.

Public Function NumberOfLeadingSpaces(ByVal theString As String) As Long
    NumberOfLeadingSpaces = Len(theString) - Len(LTrim(theString))
End Function

Public Function NumberOfTrailingSpaces(ByVal theString As String) As Long
    NumberOfTrailingSpaces = Len(theString) - Len(RTrim(theString))
End Function

Upvotes: 7

csanjose
csanjose

Reputation: 164

Sub blanks()    
    cadena = Cells(1, 1)
    i = Len(cadena)
    Do Until Mid(cadena, i, 1) <> " "
        If Mid(cadena, i, 1) = " " Then contador = contador + 1 
        i = i - 1
    Loop    
    Cells(2, 1) = contador
End Sub

Upvotes: 0

Related Questions