CODEWITHSUNDEEP
excelvbatrim
mits
mits

Reputation: 926

Trim function and WorksheetFunction.Trim do not remove leading or trailing spaces

I copied some data from a website in a worksheet and noticed some cells in addition to the expected value, also contained a trailing space.

I wanted to remove these spaces due to causing troubles to my code. I spotted a specific cell having this space and tried:

Worksheets(2).Range("D17").Value = Trim(Worksheets(2).Range("D17").Value)

Then I tried:

Worksheets(2).Range("D17").Value = WorksheetFunction.Trim(Worksheets(2).Range("D17").Value)

The Replace function not only removes leading and trailing, but also the spaces inside the string:

Worksheets(2).Range("D17").Value = Replace(Worksheets(2).Range("D17").Value," ","")

I could check the last character for being a space and remove it for every cell, but this did not seem as an elegant solution and the main question remains: Why Trim functions do not work?

Upvotes: 3

Views: 5353

Answers (1)

mits
mits

Reputation: 926

After spending a looooong time searching on the net, I found out here that there is also another character for space, the output of Chr(160) and for some reason Trim functions ignore it. So the solution in my case was firstly replace all these 'different' spaces and then trim values:

Worksheets(2).Range("D17").Value = Replace(Worksheets(2).Range("D17").Value, Chr(160), " ")
Worksheets(2).Range("D17").Value = Trim(Worksheets(2).Range("D17").Value)

In case you encounter a similar problem and run into a different-from-Chr(160) 'unknown' character, you can find out what this character is, by isolating this character and using the Asc function.

Upvotes: 9

Related Questions