Calculation from text string

matthew.armitage

Board Regular
Joined
Nov 23, 2009
Messages
72
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
 

Some videos you may like

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
Joined
Apr 26, 2018
Messages
514
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 29, 2006
Messages
1,969
In addition to what @shaowu459 had asked (above), if your data is in A1 and down, then try this formula:
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
Joined
Apr 26, 2018
Messages
514
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 23, 2009
Messages
72

ADVERTISEMENT

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
Joined
Nov 23, 2009
Messages
72
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
Joined
Apr 26, 2018
Messages
514
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 23, 2009
Messages
72
In addition to what @shaowu459 had asked (above), if your data is in A1 and down, then try this formula:
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
 

matthew.armitage

Board Regular
Joined
Nov 23, 2009
Messages
72
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.
 

Watch MrExcel Video

Forum statistics

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