Forum Discussion

JCountryman's avatar
JCountryman
Copper Contributor
Mar 09, 2023

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...
excel
Patrick2788's avatar
Patrick2788
Silver Contributor
Mar 10, 2023
I don't believe there's enough of a pattern to draw up a formula to clean each string that comes in. We can plan for the tags that appear in <> but the part that makes it difficult is the style information contains a lot of text that will change by the cell. For example:
<span style="font-family: -apple-system, BlinkMacSystemFont, &quot;Segoe UI&quot;, Roboto, &quot;Helvetica Neue&quot;, Arial, sans-serif, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;; font-size: 14.4px;">
  • JCountryman's avatar
    JCountryman
    Copper Contributor
    Mar 10, 2023

    Patrick2788 I know Plans and Options are getting a contant so can you use those as delimiters?

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Mar 10, 2023

    JCountryman 

    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.

  • JCountryman's avatar
    JCountryman
    Copper Contributor
    Mar 10, 2023

    Patrick2788  You were able to strip out the html and css before. Is that a problem now because of my changes?

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Mar 10, 2023
    I'm afraid it's the same issue. I've explored online tools that can make the CSS neater to an extent but it doesn't do us any favors for working with it in Excel. The best solution may be a tool that can extract text from CSS.
  • JCountryman's avatar
    JCountryman
    Copper Contributor
    Mar 10, 2023

    Patrick2788 

     

    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?

Resources