Hi. I want to extract text between every instance of the html bullet point markers <li> and </li> so I can separate bullet points. I am thinking it's like the SUBSTITUTE function, since that can substitute between the the two strings in the nth instance. THanks!
Welcome to the forum.
Assuming the text is in A2, try this in B2 drag copied to the right:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"<li>","|"),"</li>","|"),"|",REPT(" ",LEN($A2))),(COLUMNS($A:A)-1)*LEN($A2)+1,LEN($A2)))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks, but all I got was the text before the first <li>. I know what dragged copied to the right means but I'm not sure how I am supposed to apply that. I just entered the formula into B2 to get the result I got.
Please attach a sample workbook so we can take a look.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
You enter it into B2, then you grab the little black box in the bottom right of the cell and drag it to the right to copy the formula to the other cells in the row.I just entered the formula into B2 to get the result I got.
Last edited by AliGW; 07-21-2019 at 02:43 AM.
I uploaded a file. It works but the correct info is in the 2nd, 4th, 6th column dragged across to. If you don't want to fix it I can work with it anyway
Why would I NOT want to fix it? This is a HELP forum!
Let me take a look ...
Try this modified version:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"<li>","|"),"</li>","|"),"||","|"),"|",REPT(" ",LEN($A2))),(COLUMNS($A:A)-1)*LEN($A2)+1,LEN($A2)))
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Just fix it a little.
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"<li>","|"),"</li>","|"),"|",REPT(" ",LEN($A2))),(COLUMNS($A:A)*2-1)*LEN($A2)+1,LEN($A2)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks