1

I have an excel spreadsheet containing a list of strings in one column. The list of strings is made up of several numbers from varying lengths, separated by “/” and “;” The first entry of the string is a code id (which always has a length of 3)(red in example) followed by an “/” then an amount (which varies in length)(green in example) followed by an “;” if the string continues. Is there a way to isolate every amount in the string, is this possible with only using built in Excel functions (no VBA)

Examples:

151/802;259/22;283/93                       ---> 802 22 93
251/489;166/11;280/12;018/50;274/27;283/93  ---> 489 11 12 50 27 93

enter image description here

Up until now I’ve tried

Dutch Version:

=DEEL(E2;(VIND.SPEC("/";E2)*1)+1;(VIND.ALLES(";";E2;1)-(VIND.ALLES("/";E2;1)+1)))

Or in English:

=MID(E2;(SEARCH(“/”;E2)*1)+1;(FIND(“;”;E2;1)-(FIND(“/”;E2;1)+1)))

But this only gives me the first entry and I don’t know how loop it for the complete string.

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Den8
  • 47
  • 5

1 Answers1

1

With Excel O365:

enter image description here

Formula in B1:

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,"/",";"),";","</s><s>")&"</s></t>","//s[position() mod 2 = 0]"))

With Older version's (but at least 2013), formula in B1:

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE($A1,"/",";"),";","</s><s>")&"</s></t>","//s[position() mod 2 = 0]"),COLUMN(A1))

Drag right in case you are using this version.

If your goal was to just isolate these amounts to sum them:

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,"/",";"),";","</s><s>")&"</s></t>","//s[position() mod 2 = 0]"))

enter image description here

If you are interested in what FILTERXML() can do for you, you may find this an interesting read.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Very much appreciated! I will definitely look into it, do you know any further sources for more information about it? – Den8 Nov 04 '20 at 14:59
  • There is more information and links right there for you to study. If the answer helped don't forget to accept it too @Den8 – JvdV Nov 04 '20 at 15:16
  • I’ve bookmarked your link already and I’ll definitely use it more often from now on. I have one more question, if I wanted to use another formula on every specific output, (for example: if the output is >100, then multiply by 1.02.), how could I best do that? Is this possible with XPATH 1.0? @JvdV – Den8 Nov 05 '20 at 11:11
  • You'd need another question where you explain this in more detail with your own attempt @Den8. But from what you mentioned, yes that's possible! – JvdV Nov 05 '20 at 11:43
  • [Question](https://stackoverflow.com/questions/64698203/excel-add-an-excel-formula-within-filterxml) I've made another question with more details. Thank you for your help! @JvdV – Den8 Nov 05 '20 at 13:30