Trim Function not working in VBA

[gnaga]

I am using Trim Function in VBA to remove all spaces in a string stored in a cell.

My piece of code is like this

Adr = Application.WorksheetFunction.Trim(Cells(2, Target.Column))

Also I have tried this

 Adr = Trim(Cells(2, Target.Column))

But in both the cases I am getting the value into the variable Adr with the spaces as it is in the cell.

What could be the problem? I am breaking my head on this simple issue.

TIA & Regards
GNaga

 

[al_b_cnu]

Hi,

Application.WorksheetFunction.Trim will remove all leading, trailing & multiple internal spaces

Trim will remove all leading & trailing spaces

Application.Worksheetfunction.substitute(Cells(2, Target.Column)," ","")
will remove ALL spaces.

HTH

Alan

 

[gnaga]

Thank you Alan.

Your solution using substitute works fine.

One thing I could'nt get you. What is the meaning of multiple internal spaces? My string also have multiple spaces between words but not on the leading and trailing ends.

Thank you so much for your timely help.

Regards
GNaga

 

[al_b_cnu]

Hi

If you had a string:
[space] A [space] [space] B [space]

Application.Worksheetfunction.Trim would convert it to
A [space] B

But Trim would convert it to
A [Space] [Space] B

HTh

Alan