Excel Formula to Sum Delimited Values in Cells

Question

I have two general columns of text stored in Excel, where each cell contains numbers delimited by a semicolon (delimited only if multiple numbers present). The two Excel columns always have the same number of values delimited.

I'm trying to figure out how to sum (subtract) each value to form a delimited list in another column, especially using Excel Functions only (e.g. no VBA if possible).

How can I do this? I'm missing VBA's split function from the Excel Formulas. Note: Value - Offset = (Desired Result)

You say Add but your results say Minus – Scott Craner Sep 12 '18 at 14:38 — Scott Craner, Sep 12 '18 at 14:38
Correct, I meant minus. I'll edit. – Travis Bennett Sep 12 '18 at 15:32 — Travis Bennett, Sep 12 '18 at 15:32

score 3 · Accepted Answer · answered Sep 12 '18 at 15:01

Use this Array Formula:

=TEXTJOIN(";",TRUE,TRIM(MID(SUBSTITUTE(A2,";",REPT(" ",99)),(ROW($A$1:INDEX(A:A,LEN(A2)-LEN(SUBSTITUTE(A2,";",""))+1))-1)*99+1,99))-TRIM(MID(SUBSTITUTE(B2,";",REPT(" ",99)),(ROW($A$1:INDEX(A:A,LEN(B2)-LEN(SUBSTITUTE(B2,";",""))+1))-1)*99+1,99)))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode.

It requires the the number of inputs match the number of Offsets

score 2 · Answer 2 · answered Jul 21 '20 at 19:01

2

One could also use:

=TEXTJOIN(";",,FILTERXML("<t><s>"&SUBSTITUTE(A2,";","</s><s>")&"</s></t>","//s")-FILTERXML("<t><s>"&SUBSTITUTE(B2,";","</s><s>")&"</s></t>","//s"))

answered Jul 21 '20 at 19:01

JvdV

70,606
8
39
70

1

going back to the old ones with your new fangeled coding, I see. :) – Scott Craner Jul 21 '20 at 19:29
1

I am a fan. I just do not go back on my older questions and update with current knowledge. Too many answers to do that. – Scott Craner Jul 21 '20 at 19:53
1

Oh yes for sure, if only we knew back then what we know now. Let's call these answers of mine a near useless pastime hehe @ScottCraner. I learned something cool though today in XPATH. Posted a question on how to both call child and it's child parents node. Implemented it quite nicely [here](https://stackoverflow.com/a/63003541/9758194) – JvdV Jul 21 '20 at 19:56
1

they are never useless as who knows who will come after this and use this information. – Scott Craner Jul 21 '20 at 19:56

score 0 · Answer 3 · answered Nov 21 '22 at 15:57

I know, I am late, and it has already been solved, while searching in here, to solve an equation found this post. Both the solutions posted above by SCOTT CRANER Sir & JvdV Sir worked for me, I am just adding an alternative which works with MS365 using TEXTSPLIT()

• Formula used in cell D2

=TEXTJOIN(";",,TEXTSPLIT(A2,";",,1)-TEXTSPLIT(B2,";",,1))

Excel Formula to Sum Delimited Values in Cells

3 Answers3