Excel - Split String from Cells

Question

I have a large CSV with a couple of columns containing HTML over hundreds of rows. I need to separate certain parts into new columns. For example, a cell can contain:

<style>.some-class{property:value;}</style><div class="title">This is a title</div><div class="description">This is a description></div>

I want to move the contents of title and description into separate columns, while removing all markup.

Is there way to do this using formulas rather than macros or VBA?

Thanks

Answer 1

For the Description: (Output: This is a description)

=REPLACE(REPLACE(REPLACE(F9,FIND("This is a title",F9,1),LEN("This is a title"),""),1,LEN(".some-class{property:value;}"),""),LEN(REPLACE(REPLACE(F9,FIND("Thisis a title",F9,1),LEN("This is a title"),""),1,LEN(".some-class{property:value;}"),"")),1,"")

For the Title: (Output: This is a title)

=REPLACE(REPLACE(REPLACE(F9,FIND("This is a description",F9,1),LEN("This is a description"),""),1,LEN(".some-class{property:value;}"),""),LEN(REPLACE(REPLACE(F9,FIND("This is a description",F9,1),LEN("This is a description"),""),1,LEN(".some-class{property:value;}"),"")),1,"")

It is not very solution but you can consider changing the strings inside to formula to get your desired string. It can do (though time-consuming) if you still can't find the best solution.

Answer 2

To prevent super long formulas, may be easier to split formulas over two columns and hide the column if you want. For example, if you text is the A column, then put these formulas in the following cells: into B1:

=RIGHT(A1,LEN(A1)-FIND("class=""title"">",A1)-13)

into C1:

=LEFT(B1,FIND("</div",B1)-1)

into D1:

=RIGHT(A1,LEN(A1)-FIND("class=""description"">",A1)-19)

into E1:

=LEFT(D1,FIND("</div",D1)-1)

then copy\fill down.