Trim function is not removing multiple spaces within a string

[johnnyL]

I am pulling my hair out over this one.

I have a name that should be LastName1(space)LastName2(comma)(space)FirstName

Example: De Lahoya, Oscar

For some reason the Trim function 'apparently' is leaving two spaces between the two last names.

Sub PeskyName()
'
    Dim PeskyName   As String
'
    PeskyName = " De  Lahoya, Oscar "
'
    Debug.Print PeskyName
    Debug.Print Trim(PeskyName)
End Sub

I have also tried an ASCII check:

                    Case 65 To 90, 97 To 122, 32, 44 To 45: 'Keep only Upper/Lowercase, space, comma, hyphen ... skip/strip anything else found in the name

Still shows '2 spaces' between the last names.

I just don't know what else to try.

Any ideas?

 

[Hileolane]

Sub PeskyName()
'
    Dim PeskyName   As String
'
    PeskyName = " De  Lahoya, Oscar "
'
    Debug.Print PeskyName
    
    Debug.Print Replace(Trim(PeskyName), "  ", " ")
End Sub

TRIM only works for space in the beginning or the end of the string. So after TRIM, Replace two space into one will work.

 

[Alex Blakenburg]

I usually just use the worksheet function.

Debug.Print Application.Trim(PeskyName)

 

[johnnyL]

Sub PeskyName()
'
    Dim PeskyName   As String
'
    PeskyName = " De  Lahoya, Oscar "
'
    Debug.Print PeskyName
   
    Debug.Print Replace(Trim(PeskyName), "  ", " ")
End Sub

TRIM only works for space in the beginning or the end of the string. So after TRIM, Replace two space into one will work.

Crap! I falied to read the fine print.

There is one important thing to know when using the TRIM function in VBA –  it will only remove the leading and trailing spaces. It will not remove the extra spaces in between words (which the in-built TRIM function in the worksheet does).

Double crap! Thank you @Hileolane for steering me into a new direction.

 

[johnnyL]

I usually just use the worksheet function.

Debug.Print Application.Trim(PeskyName)

Thank you @Alex Blakenburg & @Hileolane!

Dang, I wasted time trying to code around the Trim function, I failed to see the difference between the vba and worksheet functionality of the Trim function.

I now see why my workaround also failed.

 

[Alex Blakenburg]

And for some reason that I don't understand, if you use Application.Trim you can use IsError while if you use WorksheetFunction.Trim or Application.Worksheet.Trim you have to use VBA error handling ie On Error and Err =.

This is true for using all WorksheetFunctions in VBA.
See Chip (Charles) Pearson's article here:
Calling Worksheet Functions In VBA

 

[johnnyL]

Leave it to microsoft to not dot all of the 'i's.

That is why they get paid the big $$$. Job security with needed fixes. LOL