Mar 09 2023 07:08 AM
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
Mar 09 2023 08:18 AM - edited Mar 09 2023 09:28 AM
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>"))
Mar 09 2023 09:17 AM
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
Mar 09 2023 09:28 AM
Mar 09 2023 12:55 PM
Patrick Im not having much luck trying to emulate what you created. Could you please take a look
Mar 09 2023 01:46 PM
Mar 09 2023 01:53 PM
@Patrick2788 Thanks Patrick
Mar 10 2023 12:21 AM
Mar 10 2023 06:19 AM
@Patrick2788 Did you have any luck with this?
Mar 10 2023 06:24 AM
Mar 10 2023 06:35 AM
This is a full list. I think we could use Plan and Optional as a pattern to clean each string.
For the css styling we can delete it all so it is just text. I have to export the files out in a text format which I need to do a data merge in indesign
Will this work with your script?
Mar 10 2023 08:09 AM
Mar 10 2023 08:12 AM
@Patrick2788 You were able to strip out the html and css before. Is that a problem now because of my changes?
Mar 10 2023 10:56 AM
There's too much variation in the CSS. It takes a ridiculous formula to go from the below left to the below right for the Plan:
I'm limited with a choice of delimiters for TEXTBEFORE/TEXTAFTER because some of those tags appear elsewhere or with additional tags. There's not enough of a pattern to pick the text out cleanly. As you can see from the above, there's still a few tags in the 'after'. SUBSTITUTE can pull those out, but how many different possibilities might there be to look for with the CSS?
The benefits information is more difficult because it contains more style CSS that often changes the name of the font type and size. Lots of things to look for - theoretically possible but if I'm writing the formula for all scenarios it becomes a question if manual extraction would be easier.
Mar 10 2023 11:00 AM
@Patrick2788 I know Plans and Options are getting a contant so can you use those as delimiters?
Mar 11 2023 11:49 AM
@peiyezhu for the plan and option we would need a special character ">" in-between the each bullet in the "Plan" and "Option" cells.
Is there a way to run a script so this is automated? There will be a total of 8-10 Long descriptions.
Let me know if this can be done
Mar 11 2023 02:51 PM
Mar 11 2023 07:13 PM
@peiyezhu This is way to complicated for me. is there a easier way to do this or can you create a automation to run this on my excel sheet?
Mar 11 2023 07:30 PM