Special Character - Remove Nonbreaking Space

K

k_v_deepu

New Member
Joined
Aug 25, 2008
Messages
32
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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.
 
Upvote 1
RoryA said:
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.
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Find/Replace - this is the most excellent help for blankety blank nonbreaking spaces - and whatever char(202) is! :) Just wonderful - I love this site!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
813
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
576
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
R
  • Question
Random
Replies
1
Views
438
Fluff
Fluff

Forum statistics

Threads
1,214,424
Messages
6,119,404
Members
448,893
Latest member
AtariBaby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top