Trailing space issue

[Alisya]

Hi, the below code removes trailing spaces, but for some reason it does not remove space from this, i clicked F2 and the space is still there, why does it work on some but not others?

<TABLE style="WIDTH: 60pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=80 border=0><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 60pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=80 height=17>98956P102 </TD></TR></TBODY></TABLE>

Sub test()
With ActiveSheet.UsedRange
    .Value = Evaluate("if(" & .address & "<>"""",trim(" & .address & "),"""")")
End With
End Sub

 

[Norie]

Are you sure it is a space?

 

[Alisya]

Are you sure it is a space?

There is a space on both sides of the code

 

[Norie]

What result(s) do you get when you enter =CODE(LEFT(A1)) and =CODE(RIGHT(A1)) ?

Change A1 to suit.

 

[Alisya]

What result(s) do you get when you enter =CODE(LEFT(A1)) and =CODE(RIGHT(A1)) ?

Change A1 to suit.

Formula returns 160 for RIGHT
Formula returns 32 for LEFT

 

[Norie]

Well 160 isn't actually a space and TRIM won't be able to deal with it.

 

[rconverse]

Can't you use find " " and replace with ""?

 

[Alisya]

Can't you use find " " and replace with ""?

I tried that and i got a message microsoft cannot find match

 

[Ron Coderre]

Try this....
Select the range of cells to be impacted

From the Excel Main Menu:
Edit.Replace
...Find what: Hold down ALT...type: 0160...Release ALT
...Replace with: ...leave this area blank...
Click: Replace All

Does that help?

 

[Alisya]

Try this....
Select the range of cells to be impacted

From the Excel Main Menu:
Edit.Replace
...Find what: Hold down ALT...type: 0160...Release ALT
...Replace with: ...leave this area blank...
Click: Replace All

Does that help?

Awsome, that worked, now how can incoporate this into a code?