Special Character - Remove Nonbreaking Space

[k_v_deepu]

Hi

i have downloaded some data to excel. it has got leading Nonbreaking Spaces as shown below

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" width=256><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #969696 0.5pt solid; WIDTH: 192pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=256 height=17>CLEANSERS</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #969696 0.5pt solid; WIDTH: 192pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=256 height=17> LOTIONS/MILKS/CREAMS</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #969696 0.5pt solid; WIDTH: 192pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=256 height=17> OTHER</TD></TR></TBODY></TABLE>

Can anyone help me in removing this
i tried find and replace method in excel to remove space
and also tried trim function but it didn't work

thanks

 

[xld]

=substitute(a1,char(160),"")

 

[RoryA]

You can also use the Find and Replace box. Click in the Find box, hold Alt key and type 0160 on the number keypad then release Alt; leave the Replace box blank, and hit Replace All.

 

[k_v_deepu]

thanks, its working fine

 

[GGreene]

You can also use the Find and Replace box. Click in the Find box, hold Alt key and type 0160 on the number keypad then release Alt; leave the Replace box blank, and hit Replace All.

OKAY, I spent the last 9 hours searching macros, writing macros, find and replacing everything but this. This is it!!!!!!! IT WORKS!!! THANK YOU!!!!!!!!! I have over 6000 cells that when the data was downloaded from the company's web site had the CHAR 0160. I did the code search and found it but did not know how to get rid of it from all 6000+ cells. This very simple find and replace did the job in about 3 seconds. No crap! I am using Microsoft Office 2013. I am sure that this would also work on other hidden spaces if you just use the code search and figure out what your spaces are.

 

[olives]

This is it! IT WORKS!.. This very simple find and replace did the job in about 3 seconds. No crap!...

I feel the same way. Thank you!

 

[xdriver]

I have this same issue but with character 202 which is an E with a circumflex. I have over 100,000 cells with it in there and for some reason am unable to use the find and replace using a MAC with excel 2011. I posted this below on another excel forum, but given all the posts about PCs I figured more sites are better.

I have a document that has trailing spaces of some nature. I seem to have narrowed it down to the unicode &#202 which is called an E with a cricumflex. It is located as the last character of every cell, which was imported values from a webpage. I can get rid of it using =SUBSTITUTE(A1,CHAR(202),"") but I have over 100,000 cells to do this to, so creating new columns for this is not reasonable.


I am trying to use the find and replace option but am unable to make it work. I think the trouble is osx and typing the hidden character.


Can anyone help.


Again, this is on a mac, so I don't know if that differs from windows.




Thank you VERY much.

 

[RoryA]

Have you tried copying the last character from the formula bar in one of the offending cells so you can paste it into the Find/Replace dialog?

 

[xdriver]

I did try that and it wouldn't paste for some reason. I just solved this with the help of a post from another forum

I created a cell with =char(202) and then copied the cell into the find and replace box. This pasted the hidden character that I was having trouble typing. It replaced nearly 1,000,000 instances of it.

 

[legreenwood]

Find/Replace - this is the most excellent help for blankety blank nonbreaking spaces - and whatever char(202) is! Just wonderful - I love this site!