Remove control characters in the cells

I am trying to upload the data in Excel onto a system converting the data into html format.. As the data has line spaces (due to 'Enter'), it actually is invisible control characters and hence the upload errors out..

I could see the control characters (small boxes) when I filter the column using Auto filter. As the data is huge, it would be painful to filter each cell having control characters and then delete them..

Looking for easier ways to either delete the control characters or remove line spaces and make the data continuous para..

Please help.. thanks a lot.

Hi there,

Without seeing the data I'm not sure, but see if any one of the four (probability the third) formulas from here does the trick.

HTH

Robert

I have provided the sample data below..

________________
Cc: Maria Ayala
Subject: 10-51543944

User Name: abcded

Country: USA
_________________

appears as (in the filter list box)

_________________
Cc: Maria Ayala *Subject: 10-51543944**User Name: abcded**Country:
USA
_________________

[In the above, replace * with square boxes as control characters].. They are nothing but carriage returns.

I need the below result:

________________
Cc: Maria AyalaSubject: 10-51543944User Name: abcdedCountry: USA
_________________

Robert: The fourth example worked in the link you provided...

Thanks a lot for your help.

can someone send me the formula? I have the same problem and seem to spend a LOT of time removing these boxes... thanks

Can some one provide the formula for this ? i too face the same issue

You can try tis formula

=CLEAN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(127),CHAR(7)),CHAR(160),CHAR(32)),"`","")," ","")))