What formula can I apply to get what's inside single quotations and after a slash?
For example, I want to get only the names from the Folder column and put it in my Names column
It's a little hard because it's inside a single quote, this is what I'm applying but it's not working:
=MID(LEFT(A3,FIND("' ",A3)-1),FIND("/",A3) 1,LEN(A3))
Thanks
CodePudding user response:
Use TEXTAFTER()
& SUBSTITUTE()
• Formula used in cell B2
=SUBSTITUTE(TEXTAFTER(A2:A5,"/"),"'",)
Another way for older versions: using REPLACE()
& SUBSTITUTE()
• Formula used in cell C2
=SUBSTITUTE(REPLACE(A2:A5,1,FIND("/",A2:A5),""),"'",)
Lot of ways to do this, here is another approach: Using RIGHT()
, FIND()
& SUBSTITUTE()
• Formula used in cell D2
=SUBSTITUTE(RIGHT(A2:A5,LEN(A2:A5)-FIND("/",A2:A5)),"'",)
CodePudding user response:
Use TEXTBEFORE()/TEXTAFTER()
.
=TEXTBEFORE(TEXTAFTER(A2:A5,"/"),"'")
For excel 2013 to 2021 can use FILTERXML()
.
=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,"'",""),"/","</s><s>")&"</s></t>","//s[last()]")
And for all version of excel, try-
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,"'",""),"/",REPT(" ",999)),999))