# 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

#### shaowu459

##### Well-known Member

After changed the datasource, Refresh and you will get the updated result.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### shaowu459

##### Well-known Member
Remember to change the Table name Table34 to the name of your table.
Rich (BB code):
``= Table.Group(Table.TransformColumns(Table.Combine(List.Transform(Excel.CurrentWorkbook(){[Name="Table34"]}[Content][Column1],each Table.FromRows(List.Transform(Text.Split(Text.AfterDelimiter(_,": ")," ,"),(x)=>Text.Split(x," x "))))),{"Column1",Number.From}),"Column2",{"Total",each List.Sum([Column1])})``

#### mumps

##### Well-known Member
Try:
VBA Code:
``````Sub matthew()
Application.ScreenUpdating = False
Dim Val As String, Val2 As Variant, desWS As Worksheet, prod As String
Dim i As Long, ii As Long, arr As Variant, dic As Variant, qty As Long, tot As Long, key As Variant
Set desWS = Sheets("Sheet2")
arr = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
Set dic = CreateObject("Scripting.Dictionary")
For i = LBound(arr) To UBound(arr)
Val = Trim(Split(arr(i, 1), ":")(1))
Val2 = Split(Val, ",")
For ii = LBound(Val2) To UBound(Val2)
prod = Trim(Mid(Mid(Val2(ii), WorksheetFunction.Find("x", Val2(ii)), 99999), 3, 99999))
If Not dic.Exists(prod) Then
dic.Add prod, Trim(tot + Left(Val2(ii), WorksheetFunction.Find("x", Val2(ii)) - 1))
Else
dic.Item(prod) = CLng(dic.Item(prod)) + CLng(Trim(tot + Left(Val2(ii), WorksheetFunction.Find("x", Val2(ii)) - 1)))
End If
Next ii
Next i
With desWS
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
End With
Application.ScreenUpdating = True
End Sub``````
Results will be in Sheet2. Change the range to suit your needs.

Last edited:

Thank you!

#### Anthony47

##### Well-known Member

A possible solution via macro:
VBA Code:
``````Sub PistacchioAndC()
Dim FOrder As String, PandC(), PArr()
Dim cX As Long, I As Long, cComma As Long
Dim cQt As String, cPist As String, myMatch, cUB As Long
Dim SData As Range, OutTable As Range

Set SData = Sheets("Foglio5").Range("A1")       '<<< The beginning of the Source Data
Set OutTable = Sheets("Foglio5").Range("B10")   '<<< The beginning of the desired Output Table
'
ReDim PandC(1 To 2, 1 To 1)
ReDim PArr(1 To 1)
For I = 1 To 1000
If SData.Cells(I, 1) = "" Then Exit For
FOrder = FOrder & " , " & SData.Cells(I, 1)
Next I
For I = 1 To Len(FOrder)
cX = InStr(I, FOrder, "x", vbTextCompare)
If cX = 0 Then Exit For
cComma = InStr(cX, FOrder & "    ", ",", vbTextCompare)
If cComma = 0 Then cComma = Len(FOrder) + 2
cQt = Replace(Trim(Mid(FOrder, cX - 3, 3)), ",", "")
cPist = Trim(Replace(Mid(FOrder, cX, cComma - cX), "x", "", , , vbTextCompare))
myMatch = Application.Match(cPist, PArr, False)
If IsError(myMatch) Then
cUB = UBound(PArr)
ReDim Preserve PArr(1 To cUB + 1)
ReDim Preserve PandC(1 To 2, 1 To cUB + 1)
myMatch = cUB + 1
PArr(myMatch) = cPist
PandC(1, myMatch) = cPist
End If
If IsNumeric(cQt) Then PandC(2, myMatch) = PandC(2, myMatch) + CLng(cQt)
I = cX
Next I
PandC(1, 1) = "Flavour"
PandC(2, 1) = "Qty"
OutTable.Resize(UBound(PandC, 2), UBound(PandC, 1)).Value = Application.WorksheetFunction.Transpose(PandC)
End Sub``````
Put the code in a Standard Module of your Vba Project and customize the 2 lines marked <<<; then run the Sub PistacchioAndC

Bye

Edit: In the meantime, other solutions have been proposed by @mumps and @shaowu459 ; see previous messages

#### mumps

##### Well-known Member
Have you tried the macro I suggested in Post #13?

#### matthew.armitage

##### Board Regular

The code in the Macros posts 13 and 15 worked also

#### matthew.armitage

##### Board Regular
Remember to change the Table name Table34 to the name of your table.
Rich (BB code):
``= Table.Group(Table.TransformColumns(Table.Combine(List.Transform(Excel.CurrentWorkbook(){[Name="Table34"]}[Content][Column1],each Table.FromRows(List.Transform(Text.Split(Text.AfterDelimiter(_,": ")," ,"),(x)=>Text.Split(x," x "))))),{"Column1",Number.From}),"Column2",{"Total",each List.Sum([Column1])})``

I have added a date column, could you amend the query so that it sums the a total of each type by the date?

Excel challenge.xlsx
AB
1Column1Column2
2Line Item PropertiesDate
3Selection: 3 x Chocolate ,3 x Raspberry ,3 x Pistachio ,3 x Lemon ,3 x Nutella ,3 x Chocolate Mint01-Oct-2020
4Selection: 6 x Chocolate Mint01-Oct-2020
5Selection: 2 x Strawberries & Cream ,2 x Salted Caramel ,2 x Pumpkin Spice ,4 x Coffee ,2 x Rose ,2 x Vanilla ,2 x Lemon ,2 x Nutella01-Oct-2020
6Selection: 2 x Pumpkin Spice ,2 x Raspberry ,2 x Lemon01-Oct-2020
7Selection: 1 x Rose ,1 x Pistachio ,1 x Coffee ,1 x Lemon ,1 x Chocolate ,2 x Nutella ,1 x Raspberry ,1 x Chocolate Mint ,1 x Vanilla ,1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Earl Grey ,2 x Strawberries & Cream ,2 x Chocolate Chilli Mango01-Oct-2020
8Selection: 1 x Strawberries & Cream ,1 x Vanilla ,1 x Raspberry ,1 x Nutella ,1 x Salted Caramel ,1 x Chocolate Mint01-Oct-2020
9Selection: 3 x Pumpkin Spice ,3 x Chocolate Chilli Mango01-Oct-2020
10Selection: 3 x Pumpkin Spice ,2 x Chocolate Mint ,1 x Salted Caramel01-Oct-2020
11Selection: 2 x Pistachio ,1 x Coffee ,1 x Salted Caramel ,2 x Pumpkin Spice ,2 x Chocolate Chilli Mango ,2 x Earl Grey ,2 x Chocolate Mint ,2 x Vanilla ,2 x Raspberry ,1 x Lemon ,1 x Rose01-Oct-2020
12Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose01-Oct-2020
13Selection: 3 x Rose ,3 x Lemon01-Oct-2020
14Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella01-Oct-2020
15Selection: 1 x Chocolate Chilli Mango ,1 x Salted Caramel ,1 x Pumpkin Spice ,1 x Chocolate Mint ,1 x Chocolate ,1 x Coffee01-Oct-2020
Sheet1

#### shaowu459

##### Well-known Member
Since your data has column headers, so check the checkbox to make the first line as headers.

I changed the date in column B and the result is:

Book1.xlsx
ABCDEF
1Line Item PropertiesDateColumn1Column3Total
2Selection: 3 x Chocolate ,3 x Raspberry ,3 x Pistachio ,3 x Lemon ,3 x Nutella ,3 x Chocolate Mint1-Oct-201-Oct-20Chocolate3
3Selection: 6 x Chocolate Mint1-Oct-201-Oct-20Raspberry5
4Selection: 2 x Strawberries & Cream ,2 x Salted Caramel ,2 x Pumpkin Spice ,4 x Coffee ,2 x Rose ,2 x Vanilla ,2 x Lemon ,2 x Nutella1-Oct-201-Oct-20Pistachio3
5Selection: 2 x Pumpkin Spice ,2 x Raspberry ,2 x Lemon1-Oct-201-Oct-20Lemon7
6Selection: 1 x Rose ,1 x Pistachio ,1 x Coffee ,1 x Lemon ,1 x Chocolate ,2 x Nutella ,1 x Raspberry ,1 x Chocolate Mint ,1 x Vanilla ,1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Earl Grey ,2 x Strawberries & Cream ,2 x Chocolate Chilli Mango2-Oct-201-Oct-20Nutella5
7Selection: 1 x Strawberries & Cream ,1 x Vanilla ,1 x Raspberry ,1 x Nutella ,1 x Salted Caramel ,1 x Chocolate Mint2-Oct-201-Oct-20Chocolate Mint9
8Selection: 3 x Pumpkin Spice ,3 x Chocolate Chilli Mango2-Oct-201-Oct-20Strawberries & Cream2
9Selection: 3 x Pumpkin Spice ,2 x Chocolate Mint ,1 x Salted Caramel2-Oct-201-Oct-20Salted Caramel2
10Selection: 2 x Pistachio ,1 x Coffee ,1 x Salted Caramel ,2 x Pumpkin Spice ,2 x Chocolate Chilli Mango ,2 x Earl Grey ,2 x Chocolate Mint ,2 x Vanilla ,2 x Raspberry ,1 x Lemon ,1 x Rose2-Oct-201-Oct-20Pumpkin Spice4
11Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose3-Oct-201-Oct-20Coffee4
12Selection: 3 x Rose ,3 x Lemon3-Oct-201-Oct-20Rose2
13Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella3-Oct-201-Oct-20Vanilla2
14Selection: 1 x Chocolate Chilli Mango ,1 x Salted Caramel ,1 x Pumpkin Spice ,1 x Chocolate Mint ,1 x Chocolate ,1 x Coffee3-Oct-202-Oct-20Rose2
152-Oct-20Pistachio3
162-Oct-20Coffee2
172-Oct-20Lemon2
182-Oct-20Chocolate1
192-Oct-20Nutella3
202-Oct-20Raspberry4
212-Oct-20Chocolate Mint6
222-Oct-20Vanilla4
232-Oct-20Pumpkin Spice9
242-Oct-20Salted Caramel5
252-Oct-20Earl Grey3
262-Oct-20Strawberries & Cream3
272-Oct-20Chocolate Chilli Mango7
283-Oct-20Pistachio3
293-Oct-20Vanilla1
303-Oct-20Chocolate2
313-Oct-20Rose4
323-Oct-20Lemon3
333-Oct-20Pumpkin Spice2
343-Oct-20Salted Caramel3
353-Oct-20Strawberries & Cream1
363-Oct-20Raspberry1
373-Oct-20Nutella1
383-Oct-20Chocolate Chilli Mango1
393-Oct-20Chocolate Mint1
403-Oct-20Coffee1
Sheet14

The code:

Power Query:
``= Table.Group(Table.TransformColumns(Table.Combine(Table.ToList(Excel.CurrentWorkbook(){[Name="Table30"]}[Content],each Table.FromRows(List.Transform(Text.Split(Text.AfterDelimiter(_{0},": ")," ,"),(x)=>{_{1}}&Text.Split(x," x "))))),{"Column2",Number.From}),{"Column1","Column3"},{"Total",each List.Sum([Column2])})``

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