I'm having some difficulty making the trim function work.

I pull some data from a server and occassionally that data will be
padded with extra characters at the end of the string, including both
chr(32) and chr(160). I have successfully removed the chr(160) using
the replace fucntion:

variable = replace(variable,chr(160),"")

and I'm trying to use trim to remove the spaces:

variable = trim(variable)

but the space remains.

I have tried rtrim (the padding always seems to be at the end of the
string) and I also tried the following:

variable = trim(str(variable))

and with this line of code I get a "type mismatch" error message, so
I'm thinking that my data is not appearing to be a string.

Do I need to fomat the cells where this data is loaded as text.

I'm at my wits end trying to figure this out, please share your
experience!


John Keith

John,

VBA trim only removes leading and trailing spaces. Application.Worksheetfunction.Trim (or just
Application.Trim) will remove extra spaces internal to the string.

variable = Application.Trim(variable)

HTH,
Bernie
MS Excel MVP


"John Keith" wrote in message ...
I'm having some difficulty making the trim function work.

I pull some data from a server and occassionally that data will be
padded with extra characters at the end of the string, including both
chr(32) and chr(160). I have successfully removed the chr(160) using
the replace fucntion:

variable = replace(variable,chr(160),"")

and I'm trying to use trim to remove the spaces:

variable = trim(variable)

but the space remains.

I have tried rtrim (the padding always seems to be at the end of the
string) and I also tried the following:

variable = trim(str(variable))

and with this line of code I get a "type mismatch" error message, so
I'm thinking that my data is not appearing to be a string.

Do I need to fomat the cells where this data is loaded as text.

I'm at my wits end trying to figure this out, please share your
experience!


John Keith

Bernie,

Thanks for responding. I was only wanting to remove spaces padded at
the end of a string so Trim is going to work fine for me.

I did find my problem (and I've decided that when I work late at night
I'm not as good at diagnosing issues).

My code statements were more complex than the samples in my OP and I
was trying to do multiple operations in one statement and that caused
some confusion. It turns out that I was doing the Replace and the Trim
in the wrong order. If Trim is done first and if the last character of
the string is chr(160) nothing is changed in the string and since Trim
does not act on chr(160) then Trim results in no change.




John Keith