excelexcel-formulaoffice365ms-office

Excel, TEXTSPLIT, How to get the third to last element?

Text: Apache Commons Compress:1.21:1 : Apache Commons Compress : 1.21 : BLACKDUCK : No CAP

==INDEX(TEXTSPLIT("Apache Commons Compress:1.21:1 : Apache Commons Compress : 1.21 : BLACKDUCK : No CAP",":"),-3)

An error occurred.

=RIGHT(TEXTSPLIT("Apache Commons Compress:1.21:1 : Apache Commons Compress : 1.21 : BLACKDUCK : No CAP",":"),3)

its output is

ess .21 1   ss  21  CK  CAP

Thanks!

Solution

  • Try using the following formula to get the third-to-last-element:

    enter image description here

    =CHOOSECOLS(TEXTSPLIT(A1,{":"," : "}),-3)

    Or,

    =TEXTAFTER(TEXTBEFORE(A1," : ",-2)," : ",-1)

    Even it is possible with Older Functions like FILTERXML() and MID()+REPT()+SUBSTITUTE() combinations:

    =FILTERXML("<m><b>"&SUBSTITUTE(A1," : ","</b><b>")&"</b></m>","//b[3]")

    =TRIM(MID(SUBSTITUTE(A1," : ",REPT(" ",100)),COLUMN(C1)*100-99,100))