0

I have a value in a cell that contains a long strong, and I have been struggling to figure out how to extract the code without the category (in all caps).

The value:

referrer type; channel=twitter; code=tweet_recycled_OSCARS NOMS_2_3; external_link=1

What I am looking to get:

tweet_recycled_2_3

I have gotten this far (D186 is the cell value):

=MID(D186,FIND("code=",D186)+5,FIND("; external_link",D186)-FIND("code=",D186)-5)

Which gives this result:

tweet_recycled_OSCARS NOMS_2_3

Thanks!

Justin
  • 13
  • 3
  • Is it always in all caps? IS the same number of underscores? – pgSystemTester Jan 25 '23 at 22:02
  • Yes to both questions. – Justin Jan 25 '23 at 22:05
  • If you have Office 2019 (due to `TEXTJOIN`), you could try: `=TEXTJOIN("_",1,FILTERXML(""&SUBSTITUTE(MID(D186,SEARCH("code=",D186)+5,SEARCH("; external_link",D186)-SEARCH("code=",D186)-5),"_","")&"","//s[translate(.,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')=.]"))` where each node containing an upper-case character is removed. Note that the last 2 commas are commas even if your list separator is a semicolon. Learn more about `FILTERXML` [here](https://stackoverflow.com/a/61837697). It's available since Office 2013. – VBasic2008 Jan 26 '23 at 00:39

1 Answers1

0

Assuming you have the latest excel functions, this should work (leveraging your first function)...

=SUBSTITUTE(MID(A1,FIND("code=",A1)+5,FIND("; external_link",A1)
search("code=",A1)-5),INDEX(TEXTSPLIT(MID(A1,
search("code=",A1)+5,FIND("; external_link",A1)-FIND("code=",A1)-5),"_"),1,3),"")

or if you have an excel version that doesn't have such functionality....

=LEFT(SUBSTITUTE(MID(A1,FIND("code=",A1)+5,FIND("; external_link",A1)-FIND("code=",A1)-5),"_",REPT("☠",10),2),SEARCH("☠",SUBSTITUTE(MID(A1,FIND("code=",A1)+5,FIND("; external_link",A1)-FIND("code=",A1)-5),"_",REPT("☠",10),2))-1)&"_"&SUBSTITUTE(RIGHT(SUBSTITUTE(MID(A1,FIND("code=",A1)+5,FIND("; external_link",A1)-FIND("code=",A1)-5),"_",REPT("☠",50),3),50),"☠","")

Or... even better is this one from... PB&J

=LET(including,TEXTSPLIT(TEXTBEFORE(TEXTAFTER(A1,"code=",,,,""),";"),"_"),TEXTJOIN("_",1,CHOOSECOLS(including,1,2,4,5)))
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • Hi @pgSystemTester - it won't let change the cell reference after the TEXTSPLIT – Justin Jan 25 '23 at 22:52
  • It seems I don't have the TEXTSPLIT function. – Justin Jan 25 '23 at 22:54
  • I see. Do you have the sequence function? – pgSystemTester Jan 25 '23 at 22:55
  • I don't, unfortunately. – Justin Jan 25 '23 at 22:56
  • Good luck with this.... `=LEFT(SUBSTITUTE(MID(A1,FIND("code=",A1)+5,FIND("; external_link",A1)-FIND("code=",A1)-5),"_",REPT("☠",10),2),SEARCH("☠",SUBSTITUTE(MID(A1,FIND("code=",A1)+5,FIND("; external_link",A1)-FIND("code=",A1)-5),"_",REPT("☠",10),2))-1)&"_"&SUBSTITUTE(RIGHT(SUBSTITUTE(MID(A1,FIND("code=",A1)+5,FIND("; external_link",A1)-FIND("code=",A1)-5),"_",REPT("☠",50),3),50),"☠","")` – pgSystemTester Jan 25 '23 at 23:04
  • 1
    `=LET(including,TEXTSPLIT(TEXTBEFORE(TEXTAFTER(A1,"code=",,,,""),";"),"_"),TEXTJOIN("_",1,CHOOSECOLS(including,1,2,4,5)))` would be a better option for newer Excel version – P.b Jan 26 '23 at 04:47
  • @P.b yeah but I get paid by how long (and obnoxiously complex) my formulas are... so why would I want something elegant and efficient like yours? (p.s..... ) – pgSystemTester Jan 26 '23 at 06:44
  • Yeah, there's often different ways to reach to a result: `=LET(t,TEXTSPLIT(A1,{"=","_",";"}),TEXTJOIN("_",1,INDEX(t,XMATCH(" code",t)+{1,2,4,5})))` – P.b Jan 26 '23 at 07:00
  • Talking about different ways (ideas), here's my monster `=LET(Cell,A1,tAfter1,"code=",tBefore2,";",nDel,"_",nPos,3, tBetween,TEXTBEFORE(TEXTAFTER(Cell,tAfter1,,1),tBefore2,,1), nNodes,TEXTSPLIT(tBetween,nDel),nBols,SEQUENCE(,COLUMNS(nNodes))<>nPos, TEXTJOIN(nDel,0,FILTER(nNodes,nBols)))`. – VBasic2008 Jan 26 '23 at 22:56