Trim function is not removing multiple spaces within a string

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,810
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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.

VBA Code:
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:

VBA Code:
                    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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Hileolane

New Member
Joined
Aug 4, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
VBA Code:
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,028
Office Version
  1. 365
Platform
  1. Windows
I usually just use the worksheet function.
VBA Code:
Debug.Print Application.Trim(PeskyName)
 
Solution

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,810
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
VBA Code:
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.

VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
3,810
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I usually just use the worksheet function.
VBA Code:
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. :rolleyes:

I now see why my workaround also failed.
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,028
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
3,810
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Forum statistics

Threads
1,175,733
Messages
5,899,157
Members
434,750
Latest member
XLPandit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top