find and remove specific information from column

[lines_michael]

I hope that there is an answer to this.

I have a column 'AE' that contains the html code for ecommerce item descriptions. While importing/exporting back and forth with another program and my spreadsheet, the following was added all throughout the descriptions.

%0d%0a

In some places, it may be three or four times consecutively

%0d%0a%0d%0a%0d%0a%0d%0a

Is there a macro script I could run to simply remove this any time it occurs in this column? It is just showing up as text and I am sure in another program it was representing something other than text, probably a space or hard return. Regardless, I don't need it.

See example below:

<SPAN class=body>%0d%0a<P>The <STRONG>HNC-1&nbsp;</STRONG>brushed aluminum finish cover.</P>%0d%0a<P>Pictured bottom</P>%0d%0a<P>The <STRONG>HNC-1-P</STRONG> polished aluminum finish cover.</P>%0d%0a<P>Pictured top</P>%0d%0a<P>Both models are for the HND-3, HND-4 and HND-7 cruise control handles. These&nbsp;covers can also be used with the HND-1 by modifying the back side of the cover.</P></SPAN>

Thanks in advance.

[NBVC]

Have you tried EDIT|REPLACE

Find What: %0d%0a

Replace With: (leave blank)

[lines_michael]

Yeah. I tried the find replace and it said that the formula was too long.

[NBVC]

I tested it on the example you provided and it worked...

Another way would be a helper column formula like:

=SUBSTITUTE(A1,"%0d%0a","")

copied down. Where A1 contains first string to fix.

[Ocean Zhang]

The Find/Replace function worked for me when I tested it.

Ctrl+H
Find What: %0d%0a
Replace With: Blank

I didn't get a message stating it was too long...

[lines_michael]

That did work on some of them, including the example I gave. There are others that are much longer and I am getting the error on those.

The helper column worked perfect with this exception.

I need the actual html code that I am getting by using the helper column to be in the final column, not the formula that I used to get it. (substitute(A1, "...)

Now that I have the modified html code in my helper column, how can I:

copy the actual output back to my original column or tell the helper column to keep the data and get rid of the formula that it used to get that output.

[NBVC]

Copy that column...

go to your original column and go to Edit|Paste Special and select Values...

then delete the helper column.

[lines_michael]

I got it. Paste Special. Duh!

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save