[Solved] Formulas: TRIM() won't clear a non-breaking sp

Egad · Aug 7th 2003

I am copy-pasting data off the internet which comes into Excel with leading spaces. eg: " COMMUNICATIONS".

I cannot use =TRIM to remove the spaces as they are Char(160) 'non-breaking spaces' not the usual char(32) spaces.
I can't even use trim in VBA code to remove the spaces.

Any suggestions? I have attached a worksheet.

Egad · Aug 7th 2003

Aha!
=TRIM(SUBSTITUTE(D5,CHAR(160),""))

**Andy Pope** · Aug 7th 2003

Hi Egad,

Try this,

Code

Sub EgadTrim()
    Range("F11").Value = Trim(Replace(Range("c2").Value, Chr(160), ""))
End Sub

Cheers
Andy

Egad · Aug 7th 2003

Thank you for the VBA solution Andy.
I hadn't run into non-breaking spaces before.

**Jack in the UK** · Aug 7th 2003

Egad

Sorry buddy i cant help with a solve feed only repeated the (160)

Just a point i get loads of downloads from the web * shuved * at me and i have to solve them, i (160) before i start, worth wile, i have a UDFs that clean cells to as they are fully of junk, ODBC can be just as bad. Do you ever get squares? These can be rid of with a little clean!

I’m working on a UDF from J & Excel Solutions, might you be interesting in Beta testing from Roy and myself on http://www.excel-it.com

Kindest possible regards

Jack in the UK

[Solved] Formulas: TRIM() won't clear a non-breaking sp

Participate now!

Share