How to substring in Excel between different characters?

Question

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:

Using substitute gives me the n'th occurance and that's no good as - may be my second symbol or third
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.

`FILTERXML()` may help you. Put some sample from your actual data rather that `A+B+C+D` then show your expected output. — Harun24hr, Jun 02 '21 at 09:18
It would be very helpful to put a list of example input and output data — Nick.Mc, Jun 03 '21 at 05:50

score 1 · Accepted Answer · answered Jun 02 '21 at 09:29

1

You can try FILTERXML() function.

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

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

answered Jun 02 '21 at 09:29

Harun24hr

30,391
4
21
36

2

Yes, nice! Provided you have 65 and if you don't want to nest too many `SUBSTITUTE()` in the future, you could try: `=TRANSPOSE(FILTERXML("~~"&CONCAT(IF(ISNUMBER(FIND(MID(A1,SEQUENCE(LEN(A1)),1),"+-*/")),"",MID(A1,SEQUENCE(LEN(A1)),1)))&"~~","//s"))` or if `LET()` is available: `=LET(X,MID(A1,SEQUENCE(LEN(A1)),1),TRANSPOSE(FILTERXML("~~"&CONCAT(IF(ISNUMBER(FIND(X,"+-*/")),"",X))&"~~","//s")))` =) ++ – JvdV Jun 02 '21 at 11:01
Beautiful, just beautiful. Works perfectly. I will read the article you linked, hopefully, I won't have to reask. I see how my question was a bit ... lax on details. I only need not to figure out how I can get each one individually to implement in my other formula. – Micsa Marius Jun 02 '21 at 11:59
1

Figured it out: "//s[position()=x]". THANK YOU HARUN !!! – Micsa Marius Jun 02 '21 at 12:05

score 0 · Answer 2 · answered Jun 02 '21 at 12:12

For lower versions of Excel following formula would work by copying across as much as needed:

=TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"+"," "),"-"," "),"/"," "),"*"," ")," ",REPT(" ",99)),99*COLUMNS($A1:A1),99))

which is fairly similar to what has been posted on Exceljet site.

How to substring in Excel between different characters?

2 Answers2