# Calculation from text string

#### matthew.armitage

##### Board Regular
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### shaowu459

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

#### Anthony47

##### Well-known Member
Excel 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

##### Well-known Member
Do you need the following result?
Book1.xlsx
ABC
1Column1
2Selection: 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
3Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella
4Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose
5
6Column2Total
7Rose2
8Pistachio4
9Coffee2
10Lemon2
11Chocolate3
12Nutella2
13Raspberry2
14Chocolate Mint1
15Vanilla2
16Pumpkin Spice2
17Salted Caramel4
18Earl Grey1
19Strawberries & Cream2
20Chocolate Chilli Mango1
Sheet15

#### matthew.armitage

##### Board Regular

Data:

Excel challenge.xlsx
A
2Selection: 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
3Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella
4Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose
Sheet3

Expected results:

Excel challenge.xlsx
DE
6Rose1
7Pumpkin Spice2
8Pistachio4
9Salted Caramel4
10Vanilla2
11Coffee2
12Strawberries & Cream2
13Chocolate3
14Lemon2
15Raspberry2
16Rose1
17Nutella2
18Chocolate Mint1
19Earl Grey1
20Chocolate Chilli Mango1
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

##### Board Regular
Do you need the following result?
Book1.xlsx
ABC
1Column1
2Selection: 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
3Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella
4Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose
5
6Column2Total
7Rose2
8Pistachio4
9Coffee2
10Lemon2
11Chocolate3
12Nutella2
13Raspberry2
14Chocolate Mint1
15Vanilla2
16Pumpkin Spice2
17Salted Caramel4
18Earl Grey1
19Strawberries & Cream2
20Chocolate Chilli Mango1
Sheet15

Yes!

#### shaowu459

##### Well-known Member

Data:

Excel challenge.xlsx
A
2Selection: 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
3Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella
4Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose
Sheet3

Expected results:

Excel challenge.xlsx
DE
6Rose1
7Pumpkin Spice2
8Pistachio4
9Salted Caramel4
10Vanilla2
11Coffee2
12Strawberries & Cream2
13Chocolate3
14Lemon2
15Raspberry2
16Rose1
17Nutella2
18Chocolate Mint1
19Earl Grey1
20Chocolate Chilli Mango1
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

##### Board Regular
Excel 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

##### Well-known Member
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

##### Board Regular
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.

Replies
1
Views
255
Replies
3
Views
93
Replies
8
Views
145
Replies
0
Views
50
Replies
5
Views
346

1,118,520
Messages
5,572,621
Members
412,475
Latest member
JaredNAU