Remove specific text from only certain cells

**amoorman** · 02-09-2009, 07:00 AM

I am exporting lots of data from a website and for some reason to do with html i am getting &nbsp; in a few cells in my description column (column C), i think that means "& " in html. this occurs in some cells but not others when i paste it into Excel. I would like to remove these characters.

I know I can do it with crtl H and the replace field blank but I would like a formula so in the future the user will simply have to paste information and then get converted information out (as they will struggle to use excel).

I think i need to use a combination of IF, FIND, REPLACE, LEN but i am struggling.

In a follow up problem some cells contain &amp which i believe is &, i would also like to convert this &amp back to &.

Many Thanks for any help

Ashley

**Rahul Nagar** · 02-09-2009, 07:06 AM

Hello, amoorman

Can you please attach a sample file for your problem and please tell what result do you want.

**amoorman** · 02-09-2009, 07:29 AM

Hi thank you very much,

As you can see in column B there is some irrelevant data. How can i remove this?

Many thanks

Ashley

**Richard Schollar** · 02-09-2009, 08:31 AM

Hi Ashley

try this formula in C2 copied down:

=SUBSTITUTE(SUBSTITUTE(B2,"&",""),"nbsp;","")

Richard

**amoorman** · 02-09-2009, 08:48 AM

AH! I had never used substitute before.
Your solution is nearly perfect however the where &amp was present that was removed and not changed to & (probably due to my poor explanation).

This solution solves that issue as well.

=SUBSTITUTE(SUBSTITUTE((SUBSTITUTE(B8,"&","&")),"&",""),"nbsp;","")

Thank You!