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

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

"robotman" wrote in message oups.com...
|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
|

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

"robotman" wrote in message
oups.com...
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

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


"Sasa Stankovic" wrote in message
...
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.