I have a data set with 5 types of entry that begin with <xxxx>value i want</xxxx>. What I'd like to do is have an if statement that determines what the xxxxx type is and then returns the value between the >< marks. I've got the if statement part figured out, but I cant figure out how to extract the internal value/trim the tail because the number of characters varies.
Please see attached for further clarification
if_statement_results_between_chars.xlsx
Thank you!
Try
=MID(B3,FIND(">",B3)+1,FIND("^",SUBSTITUTE(B3,"<","^","2"))-FIND(">",B3)-1)
I don't know how I came up with that formula.....but it works
Maybe this
=MID(A2,FIND(">",A2)+1,FIND("</",A2)-FIND(">",A2)-1)
Try:
=MID(B3,(FIND(">",B3,2))+1,(FIND("<",B3,2))-(FIND(">",B3,2))-1) and copy down.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
If you want to avoid formula, use TextToColumn, TWICE.
Does it have to be done with a formula? I managed to achieve the desired results by using Data>Text to Columns and delimiting the data first on ">" and then "<"
Thank you all, these all work very nicely
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks