How to get the text inside single quotations-CodePudding

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))