Trim hidden characters?

[robotman]

I have an imported table and several of the fields have extra line
feeds or other hidden characters. This makes it impossible to sort
the table.

Is there any sort of way to trim off these extra characters at the
spreadsheet level so I can do a sort? I can write a custom function
to remove anything that's not A-Z,0-9 but if there's a feature at the
spreadsheet level, that would be great.

Any ideas?!

Thanks.

John

 

[Niek Otten]

Hi John,

Maybe this is of use to you:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have an imported table and several of the fields have extra line
| feeds or other hidden characters. This makes it impossible to sort
| the table.
|
| Is there any sort of way to trim off these extra characters at the
| spreadsheet level so I can do a sort? I can write a custom function
| to remove anything that's not A-Z,0-9 but if there's a feature at the
| spreadsheet level, that would be great.
|
| Any ideas?!
|
| Thanks.
|
| John
|

 

[Sasa Stankovic]

Hi,
You can use function TRIM or/and CLEAN.
TRIM removes extra spaces around text (all spaces before and after text and
leaves only one space between words. CLEAN removes any nonprinting
characters.
So, you'll get some extra columns but your imported data will be filtered.

sasa

 

[Peo Sjoblom]

Not entirely true, clean does not remove trailing html char 160 which seems
to be fairly common when data is copied from a web site


--


Regards,


Peo Sjoblom