Calculation from text string

[matthew.armitage]

Hi All

I have got a web report that exports to a text string with the quantity and product. Its always in the same format, starts with "Selection:" and the the "quantity" "x" "product" and then comma. A couple of examples are below:

Selection: 1 x Rose ,1 x Pistachio ,2 x Coffee ,2 x Lemon ,2 x Chocolate ,1 x Nutella ,1 x Raspberry ,1 x Chocolate Mint ,1 x Vanilla ,1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Earl Grey ,1 x Strawberries & Cream ,1 x Chocolate Chilli Mango
Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella
Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose

What is the best way to get a grand total for each type for the entire list.

I can do it do as a one off excerise using text to colums but takes ages, so would like to automate so I can paste the report into my file and sum automatically.

Thanks

Matt

 

[shaowu459]

Can you upload your data using XL2BB tools or a screenshot with expected results? Do you accept a Power Query solution?

 

[Anthony47]

In addition to what @shaowu459 had asked (above), if your data is in A1 and down, then try this formula:

=SUM(--SUBSTITUTE(MID(A1,FIND("#",SUBSTITUTE(A1,"x","#",ROW(INDIRECT("1:" & LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))))))-3,3),",",""))

You need to confirm it using the keys Contr-Shift-Enter, non Enter alone

Bye

 

[shaowu459]

Do you need the following result?

Book1.xlsx
	A	B	C
1	Column1
2	Selection: 1 x Rose ,1 x Pistachio ,2 x Coffee ,2 x Lemon ,2 x Chocolate ,1 x Nutella ,1 x Raspberry ,1 x Chocolate Mint ,1 x Vanilla ,1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Earl Grey ,1 x Strawberries & Cream ,1 x Chocolate Chilli Mango
3	Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella
4	Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose
5
6	Column2	Total
7	Rose	2
8	Pistachio	4
9	Coffee	2
10	Lemon	2
11	Chocolate	3
12	Nutella	2
13	Raspberry	2
14	Chocolate Mint	1
15	Vanilla	2
16	Pumpkin Spice	2
17	Salted Caramel	4
18	Earl Grey	1
19	Strawberries & Cream	2
20	Chocolate Chilli Mango	1
Sheet15

 

[matthew.armitage]

Data:

Excel challenge.xlsx
	A
2	Selection: 1 x Rose ,1 x Pistachio ,2 x Coffee ,2 x Lemon ,2 x Chocolate ,1 x Nutella ,1 x Raspberry ,1 x Chocolate Mint ,1 x Vanilla ,1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Earl Grey ,1 x Strawberries & Cream ,1 x Chocolate Chilli Mango
3	Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella
4	Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose
Sheet3

Expected results:

Excel challenge.xlsx
	D	E
6	Rose	1
7	Pumpkin Spice	2
8	Pistachio	4
9	Salted Caramel	4
10	Vanilla	2
11	Coffee	2
12	Strawberries & Cream	2
13	Chocolate	3
14	Lemon	2
15	Raspberry	2
16	Rose	1
17	Nutella	2
18	Chocolate Mint	1
19	Earl Grey	1
20	Chocolate Chilli Mango	1
Sheet3

In terms of power query its not somthing that I have used before, so would prefer to avoid it, but if that is the only way then I will learn how to use it,

Thanks

Matt

 

[matthew.armitage]

Do you need the following result?

Book1.xlsx
	A	B	C
1	Column1
2	Selection: 1 x Rose ,1 x Pistachio ,2 x Coffee ,2 x Lemon ,2 x Chocolate ,1 x Nutella ,1 x Raspberry ,1 x Chocolate Mint ,1 x Vanilla ,1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Earl Grey ,1 x Strawberries & Cream ,1 x Chocolate Chilli Mango
3	Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella
4	Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose
5
6	Column2	Total
7	Rose	2
8	Pistachio	4
9	Coffee	2
10	Lemon	2
11	Chocolate	3
12	Nutella	2
13	Raspberry	2
14	Chocolate Mint	1
15	Vanilla	2
16	Pumpkin Spice	2
17	Salted Caramel	4
18	Earl Grey	1
19	Strawberries & Cream	2
20	Chocolate Chilli Mango	1
Sheet15

Yes!

 

[shaowu459]

Data:

Excel challenge.xlsx
	A
2	Selection: 1 x Rose ,1 x Pistachio ,2 x Coffee ,2 x Lemon ,2 x Chocolate ,1 x Nutella ,1 x Raspberry ,1 x Chocolate Mint ,1 x Vanilla ,1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Earl Grey ,1 x Strawberries & Cream ,1 x Chocolate Chilli Mango
3	Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella
4	Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose
Sheet3

Expected results:

Excel challenge.xlsx
	D	E
6	Rose	1
7	Pumpkin Spice	2
8	Pistachio	4
9	Salted Caramel	4
10	Vanilla	2
11	Coffee	2
12	Strawberries & Cream	2
13	Chocolate	3
14	Lemon	2
15	Raspberry	2
16	Rose	1
17	Nutella	2
18	Chocolate Mint	1
19	Earl Grey	1
20	Chocolate Chilli Mango	1
Sheet3

In terms of power query its not somthing that I have used before, so would prefer to avoid it, but if that is the only way then I will learn how to use it,

Thanks

Matt

Please refer to my post #4, Power Query is a more suitable tool for your case. I will post the code if you need.
By the way, there is a trailing blank after "Rose", so you get two lines of "Rose" in above post.

 

[matthew.armitage]

In addition to what @shaowu459 had asked (above), if your data is in A1 and down, then try this formula:

=SUM(--SUBSTITUTE(MID(A1,FIND("#",SUBSTITUTE(A1,"x","#",ROW(INDIRECT("1:" & LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))))))-3,3),",",""))

You need to confirm it using the keys Contr-Shift-Enter, non Enter alone

Bye

This seems to be adding the numbers toghter on each row, rather than a caclaution to sum the total for each flavour for the entire list

Thanks

 

[Anthony47]

This seems to be adding the numbers toghter on each row, rather than a caclaution to sum the total for each flavour for the entire list

Thanks

I understood the question the wrong way, let's see were the answer will arrive

 

[matthew.armitage]

Please refer to my post #4, Power Query is a more suitable tool for your case. I will post the code if you need.
By the way, there is a trailing blank after "Rose", so you get two lines of "Rose" in above post.

Well spotted on Rose

Please can you send me the code and I will do some research on how to use power query, thanks.