Forum Discussion
Cell has a list wrapped in html. Split list into 2 separated by special character
I have an excel file that has a list of items that are wrapped in html code. I would like to split it into 2 groups and move them into 2 different cells and separate the list with a special character (such as >) that I can search and replace later on.
Please see attachment. I have put notes in the excel file
26 Replies
Revised formulas:
Plan:
=LET(plan,TEXTAFTER(TEXTBEFORE(B2,$D$1),"</span><div><ul><li>"),SUBSTITUTE(SUBSTITUTE(plan,"</li><li>",">"),"</li></ul><div>",""))Benefits:
=LET(benefits,TEXTAFTER(TEXTAFTER(B2,$D$1),"</div><div><ul><li>"),cleaned,SUBSTITUTE(benefits,"</li><li>",">"),TEXTBEFORE(cleaned,"</li>"))- We can use BS4 to clean this.
Patrick I appreciate your response. Instead of a list can we set it up so that it is all one line separated by >. See column C and 7. Highlighted in red. See attached
- Yes, I've revised my post above with new formulas and a new workbook.
