VBA SUM spaces in string

[inkserious]

I have a several strings of text; each with a variable number of spaces at the end of the string. How can I sum these spaces within my Sub?

I'm very new to VBA and haven't yet learned how all the Excel function work in VBA.

Thanks for any help.

-ep

 

[Steve3110]

What exactly do you mean by sum the spaces? Do you mean you want to count how many characters are spaces?

Also, whats this needed for? Is there a specific need to count the spaces?

 

[firefytr]

I'm with Steve, you're plain old confusing. But to take your literal interpretation and 'sum' with the single criterion of spaces would make it to 'count', so if you had this in a cell "abc " and wanted the number of spaces you could use...

Formula:
=LEN(A1)-TRIM(LEN(A1))

VBA:

Dim r as range
set r = range("A1")
Msgbox Len(r.value) - Trim(Len(r.Value)) & " spaces in " & r.Addrress

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Steve3110]

just a slight correction:

Msgbox Len(r.value) - Len(Trim(r.Value)) & " spaces in " & r.Addrress
[/code/

You need to get the length of the trimmed string

 

[firefytr]

LOL! Had 'em back-ards, didn't I.. Thanks Steve.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[inkserious]

I said a variable number of spaces, but it is actually an undetermined number of spaces. I need to find how many spaces are at the end of the string and then trim that number of spaces.

Thanks.

 

[SkipVought]

How about using the Trim, LTrim or RTrim Function without counting anything?

Skip,
[sub]
[red][/red]
[/sub]

 

[Steve3110]

The function provided above would have given you the number of spaces but as SkipVought said, the Trim function will allow all spaces at the beginning and end to be removed:

Example:

dim str as string
str = "   Testing     "
output = trim$(str)

Output would then equal "Testing

 

[firefytr]

Is there a reason you need to know how many spaces are there? Why are they there in the first place? Generally these types of things are killers..

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[inkserious]

The spaces are there because the data is being pasted from and AS400 system.

Option Explicit
Dim i As Range
Dim TrimSpaces As Range


Sub ShuffleNames()
    Set TrimSpaces = Range("RngWinLoss")
    Dim FirstName As String
    Dim LastName As String
    Dim CommaLoc As Long
    For Each i In TrimSpaces
        If Not IsEmpty(i.Value) Then
            i.Value = Application.Trim(i.Value)
            CommaLoc = InStr(i.Value, ",")
            LastName = Left(i.Value, CommaLoc - 1)
            FirstName = Right(i.Value, Len(i.Value) - CommaLoc)
            LastName = Application.Proper(LastName)
            FirstName = Application.Proper(FirstName)
            i.Value = FirstName & " " & LastName
        End If
    Next i
End Sub

Got this to work with a little of help and a little less hair.

-ep

 

[firefytr]

I guess my question is what do you need those other variables when you can do it without them ...

Sub ShuffleNames()
    Dim c As Range, TrimSpaces As Range, wf As WorksheetFunction
    Dim CommaLoc As Long
    Set TrimSpaces = Range("RngWinLoss") 'what sheet??
    For Each c In TrimSpaces
        If Not IsEmpty(c.Value) Then
            CommaLoc = InStr(c.Value, ",")
            If CommaLoc <> 0 Then
                c.Value = WorksheetFunction.Proper(Trim(Right(c.Value, Len(c.Value) - CommaLoc)) & _
                    " " & Trim(Left(c.Value, CommaLoc - 1)))
            End If
        End If
    Next c
End Sub

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[inkserious]

Thanks Zack. What about this? I removed the wf As WorksheeetFunction and simply replaced with Application

Sub ShuffleNames()
    Dim i As Range, TrimSpaces As Range
    Dim CommaLoc As Long
    Set TrimSpaces = Range("RngWinLoss")
    For Each i In TrimSpaces
        If Not IsEmpty(i.Value) Then
            CommaLoc = InStr(i.Value, ",")
            If CommaLoc <> 0 Then
                i.Value = Application.Proper(Trim(Right(i.Value, Len(i.Value) - CommaLoc)) & _
                    " " & Trim(Left(i.Value, CommaLoc - 1)))
            End If
        End If
    Next i
End Sub

-ep

 

[firefytr]

I would use WorksheetFunction. Proper may work, but it's not native anymore. If anything, I'd leave the Application off the front (as we already know we're in Excel VBEIDE).

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci