Search code examples
excelsubstringformula

How to substring in Excel between different characters?

first-time poster so please bear with me. I am trying to convince Excel to do a substring and failing miserably. The task is simple enough on the surface of it, extract text that's between a fixed set of chars (+, -, * and /), basically mathematical operators. My input string looks like this:

A+B+C+D

Now, if my string looks like that, or like A-B-C-D, all is good, I can use this and it works (not my code, found on https://exceljet.net/formula/split-text-with-delimiter and modified to suit my needs:

First text: TRIM(MID(SUBSTITUTE($A2,"+",REPT(" ",LEN($A2))),0*LEN($A2)+1,LEN($A2)))
Second: TRIM(MID(SUBSTITUTE($A2,"+",REPT(" ",LEN($A2))),1*LEN($A2)+1,LEN($A2)))
Third: TRIM(MID(SUBSTITUTE($A2,"+",REPT(" ",LEN($A2))),2*LEN($A2)+1,LEN($A2)))
Forth: TRIM(MID(SUBSTITUTE($A2,"+",REPT(" ",LEN($A2))),3*LEN($A2)+1,LEN($A2)))

And all is good, until I have a string like: A-B+C-D or wahtever combo, basically not all the same char. I tried using Find and Search in different configurations, but I always come to the same problem:

  1. Using substitute gives me the n'th occurance and that's no good as - may be my second symbol or third
  2. Can't dynamically and accurately calculate the length for MID, as it does Nr. of chars, not "until"

I can't use VB script for security reasons, so I am stuck trying to use Excel formulas. It HAS to be one formula, as in the end, it's part of a bigger formula that's something like this:

CONCATENATE(IF(ISNUMBER(A),A,VLOOKUP(A)),IF(ISNUMBER(A),A,VLOOKUP(A)),IF(ISNUMBER(A),A,VLOOKUP(A)),IF(ISNUMBER(A),A,VLOOKUP(A)))

So I have the input in a cell and my result has to do all the processing in an adjacent cell. Thank you in advance, at whit's end over here.

Solution

  • You can try FILTERXML() function.

    =TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"+","|"),"-","|"),"*","|"),"/","|"),"|","</s><s>")&"</s></t>","//s"))

    enter image description here

    If you are not on Excel365 then try below formula.

    =FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"+","|"),"-","|"),"*","|"),"/","|"),"|","</s><s>")&"</s></t>","//s[" & COLUMN(A1) &"]")

    To learn FILTERXML() go through this article from @JvdV