Removing HTML from a field

[schawel]

I have a massive .csv doc.

One of the columns headers in this worksheet is called 'Description'. The cell value for every row below this header is filled with HTML. Here is an example:

<p>Some paragraph of stuff</p>
<ol>
<li>Some listing 1</li>
<li>Another listing 2</li>
</ol>

My goal is to remove all the content (HTML included) between the OL tags for each and every cell under the column 'Description' and put it directly into the cell to the right. I would call this column 'Listing'.

Macros? Formulas?

 

[syntaxed]

Would this work?




=SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("

1. ",A2),FIND("

",A2)),"

1. ",""),"

","")


EDIT: I'm having trouble posting the formula coz it contains tags, so try this and remove the spaces before each instance of "ol" or "/ol":
=SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("< ol>",A2),FIND("< /ol></ ol>",A2)),"< ol>",""),"< /ol></ ol>","")

 

[syntaxed]

=SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("<ol>",A2),FIND("</ol>",A2)),"<ol>",""),"</ol>","")

 

[schawel]

Wow that worked pretty well, however, the Sub function left the code in the description area. If I move it, I also want to delete it from the Description column so I don't have redundant content.

how would I do that?

Thanks!

 

[syntaxed]

Assuming the description column is A, the listing column is B, then in a third column, say column C, I'd use the following:

=SUBSTITUTE(A2,B2,"")

Drag down and fill for all rows, then simply copy column B and paste values in column B, then copy column C and paste values in column A, then delete column C.