Help pulling unknown amount of data from a cell containing text, into an array

[nott0m]

As an example, Given the table below, without using any helper columns, is there a way I can pull and sum the values from the second column?

Ideally, I'd like a formula that produces an array of the substrings that can then be manipulated as needed. The items will always be separated by ", ", but there could be any number of them, although a moderate cap on the number of items would be fine if needed.

Item1	1, 3, 5, 6
Item2	1, 2, 3
Item3	2, 45, 89

[Rick Rothstein]

For you sum (the argument to the SUMPRODUCT function would be your array producer)...

Please Login or Register  to view this content.

[MarvinP]

Hi nott0m,

I found a little bit of magic in Excel a few weeks ago. I was researching the "Evaluate()" function in VBA. It turns out you can put it in a Named Range formula and it will work there too!! I was completely shocked.
So to do your problem, you don't need any VBA or SumProduct formula simply look at the Named Range formula. You will need to put "=Eval" to the cell to the right of the one with the numbers in it, that are comma separated. This will stump most Excel Guru. Evaluate in Named Range Add.xlsm Click on the underline name to the left to download or open my example file.
We'll see if Rick has ever seen this when he reads my answer. Rick?

[Rick Rothstein]

I have seen that before... it's a throwback to the old Excel4Macro days if I am not mistaken. However, it does not matter that you did not stump me since I am not rated as an "Excel Guru" on this forum (I am not even rated as an "Excel Expert"... I have more than enough Reputation Points, but am still short by more than 300 postings in order to earn that designation as of now).

As to the OP's request... after seeing your formula, I realize that I misread the OP's request. When I read this... "is there a way I can pull and sum the values from the second column"... I thought he meant the second item in each cell (and that is what my formula does), but now I realize the OP meant the second column in his "table".

[MarvinP]

Being a Guru and $5 will get you a cup of coffee. I sometimes tell people I'm an Excel Guru and they immediately change the subject or turn to talk to someone else.

I think we vote the Guru's in now so you should be nice to everyone. Don't stop answering questions as I keep learning from your VBA. Hang out here more and get more karma by paying it forward. That is what we all are really working on. It is still fun to learn new stuff about Excel.

[Rick Rothstein]

Don't stop answering questions as I keep learning from your VBA.

What an incredibly kind comment for you to make... thank you so much.

[nott0m]

"is there a way I can pull and sum the values from the second column"... I thought he meant the second item in each cell (and that is what my formula does),

Yeah, I'm sorry I wasn't more clear. Essentially I was looking for would be a formula in column C that gives: 15, 6, 136

[nott0m]

OK, this seems like utter black magic. I'll need to dig into this formula to see how it actually works.

Thanks!

[nott0m]

OK, so as a follow up.

Instead of simply pulling numbers, I now want to pull text and use that to lookup the numbers in a table.

Item	Number
Item1	Num1, Num2
Item2	Num1, Num2, Num3
Item3	Num2, Num3

Number	Value
Num1	5
Num2	10
Num3	7

I tried using the same concept as your example with Range "Eval2" set as

Please Login or Register  to view this content.

Where B2 is the second column of the first table, and GetNumbers is the 2nd table.
The problem is that it seems to only return the first value.

If I put the argument of the EVALUATE function in a cell, and then paste that result as value and add an equal sign, it seems to work.

I'm guessing the EVALUATE function doesn't like arrays? Do you have any thoughts?

[CK76]

Do you mean that you want something like... below?

Item	Number	SUM
Item1	Num1, Num2	15
Item2	Num1, Num2, Num3	22
Item3	Num2, Num3	17

Assuming this table starts at A1 and the lookup table is in F1:G4

Something like below.
In C2:
=SUMPRODUCT(ISNUMBER(SEARCH($F$2:$F$4,B2))*($G$2:$G$4))

Copy down.

EDIT: If you wanted to sum "1, 3, 5, 6" like in your initial post. Another approach is to use FILTERXML() to generate array.

Ex: =SUM(FILTERXML("<a><b>"&SUBSTITUTE(B2,", ","</b><b>")&"</b></a>","//b"))

0.JPG

[nott0m]

The first one is perfect! Thanks.

The second one is also great to know and is actually the kind of thing I was kind of expecting originally. I look forward to playing around with the XML stuff a bit.