Reputation: 85
I have a working SPLIT function. The challenge is that I have excesses that I would like to remove. So basically, I have a paragraph of some sort with a delimiter which I am using to pick parts of the phrase. I am able to use the SPLIT function to extract what I need but I have too many columns which become useless after the SPLIT.
My Original Phrase:
Lorem Ipsum is title simply dummy style text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever title since the 1500s style when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic title typesetting, remaining style essentially unchanged.
I use the following formula to add a single delimiter
=SUBSTITUTE(SUBSTITUTE(A1,"title","❤️"),"style","❤️")
Phrase with delimiter
Lorem Ipsum is ❤️simply dummy❤️ text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever ❤️since the 1500s❤️ when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic ❤️typesetting, remaining ❤️essentially unchanged.
This is the formula I'm using to split.
=SPLIT(B1,"❤️",0)
So after doing this, I have what I need in separate columns but is there a way to remove the rest of the phrase totally so that the formula below just displays the required columns only?
Link to the spreadsheet: https://docs.google.com/spreadsheets/d/154Njb_tM43dWnP3iwt09na6bY_zInTDx799FQoQGefQ/edit?usp=sharing
Upvotes: 0
Views: 405
Reputation: 1
use in B1:
=SUBSTITUTE(SUBSTITUTE(A1,"title","❤️♦"),"style","❤️")
use in C1:
=INDEX(SUBSTITUTE(FILTER(SPLIT(B1,"❤️",0), REGEXMATCH(SPLIT(B1,"❤️",0), "♦")), "♦", ))
Upvotes: 2