Remove no-backspace character (xA0) in excel

[BMD4]

Hello everyone,

We are facing issue in identifying and deleting 'no-backspace character' in excel. Can any one please suggest how can we do this.

Below string has no-backspace character and is actually shown as 'CBS xA04Q20-050 Prjt Moonlight Disposition'

'CBS 4Q20-050 Prjt Moonlight Disposition'.

I have tried clean but it does not clean the spl character.

Thanks in advance

[Rick Rothstein]

If your string looks like you showed it in your post, then the "x40" are simply three characters in the string... use either Excel's Replace dialog box or VBA's Replace function (which ever environment is appropriate) to replace it with the empty string ("").

[BMD4]

Thanks for the reply Rick.

I do not see the character 'xA0' in excel.But, we are generating a notepad file out of the excel where we see the character.
How can I find out where we have no backspace 'xA0' character to replace it.

Thanks

[Rick Rothstein]

I call that character (ASCII 160) a non-breaking space. Select the column or columns with the text you want to process, call up the Excel's Replace dialog box (CTRL+H)... click into the "Find what" field, hold down the ALT key and type 160 using the Number Pad only... if you want to remove it, leave the "Replace with" field empty, otherwise put a single space character in the "Replace field" to maintain the originally intended spacing of words... click the "Options>>" button and make sure the checkbox labeled "Match entire cell contents" is NOT checked... click the "Replace All" button.

[Pepe Le Mokko]

A couple of tips for "Cleaning "Dirty" Data"