Excel Formula to Sum Delimited Values in Cells

0
votes

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. What Excel formulas can achieve the Desired Result? Note: Value - Offset = (Desired Result)

2
arraysexcelsplitformulatextjoin
You say Add but your results say Minus – Scott Craner
Correct, I meant minus. I'll edit. – Travis Bennett

2 Answers

2
votes

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

enter image description here

1
votes

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"))

enter image description here