Functions to separate a string separated by commas

[jeffdolton]

Hi,

I’ve opened up another thread as my request this time is a little different. With help from experts on this forum, and using some basic excel logic, I’ve managed to separate purchase data from an EPOS system. I would like to consolidate all of this into just two functions if possible; one to show the product purchased and one to show the number of products purchased in one transaction.

The EPOS data extract shows items purchased in one transaction and the items are separated by commas, although options selected are separated by a comma within parentheses.

An example of a single transaction is:

2 x bacon bap 2 sausages, Bottled beer 500ml, 3 x Coffee (Own Cup), Polo Shirt (Navy, Large)

In this case four products have been purchased in one transaction. A weekly EPOS extract can have hundreds of rows of transactions.

I need to separate each product purchase on the same row as follows:

Selection 1. Selection 2. ..... Selection 15
Product. No Product. No. Product No.

I’ve catered for up to 15 different products being purchased under the one transaction.

As I see it the following is required:

i. Separate each product group contained within a comma (but not the option separated by a comma in brackets).
ii. Leave the comma within the parentheses as this is how a lookup table appears . If this is too difficult then these commas can stay (there can be up to three commas within brackets)
iii. Separate the first number in the product group and if there is no number return a value of 1.
iv. Remove the space x space from the selection. .

So the output should look like this.

Selection 1. Selection 2 Selection 3. S Selection 4

Product No. Product. No. Product. No. Product. No.
bacon bap 2 sausages 2 Bottled beer 500ml. 1 Coffee (Own Cup) 3 Polo Shirt ( Navy Large 1


As always, many thanks for your help.

 

[Peter_SSs]

it worked perfectly but did take a long time to run though

each product and number is separated by two blank columns?

See how this goes, addressing both of the above.
The code allows for a maximum of 50 products per line but that can be altered if more could exist.

Sub Split_Transactions()
  Dim a As Variant, b As Variant
  Dim i As Long, maxCols As Long, PosCom As Long, Posx As Long, Qty As Long, col As Long
  Dim s As String, sTemp As String, sProd As String
 
  a = Range("L2", Range("L" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 200)
  For i = 1 To UBound(a)
    s = a(i, 1)
    col = -3
    sTemp = ReplaceCommaInParen(s) & ","
    Do Until Len(sTemp) = 0
      PosCom = InStr(1, sTemp, ",")
      sProd = Left(s, PosCom - 1)
      Posx = InStr(1, sProd, " x ")
      If Posx = 0 Then
        Qty = 1
      Else
        Qty = Left(sProd, Posx)
        Posx = Posx + 2
      End If
      col = col + 4
      If col > maxCols Then maxCols = maxCols + 4
      b(i, col) = Trim(Mid(sProd, Posx + 1))
      b(i, col + 1) = Qty
      sTemp = Trim(Mid(sTemp, PosCom + 1))
      s = Trim(Mid(s, PosCom + 1))
    Loop
  Next i
  With Range("M2").Resize(UBound(b), maxCols)
    .Value = b
    For i = 1 To maxCols Step 4
      .Cells(0, i).Resize(, 4).Value = Array("Product", "Qty", "Category", "Unit Price")
    Next i
    .EntireColumn.AutoFit
  End With
End Sub

Function ReplaceCommaInParen(ByVal s As String) As String
  Dim posStart As Long, PosOpen As Long, PosClose As Long, PosComma As Long
 
  PosOpen = InStr(posStart + 1, s, "(")
  Do Until PosOpen = 0
    PosClose = InStr(PosOpen, s, ")")
    If PosClose > PosOpen Then
      PosComma = InStr(PosOpen, Left(s, PosClose), ",")
      Do Until PosComma = 0
        Mid(s, PosComma, 1) = "^"
        PosOpen = PosComma
        PosComma = InStr(PosOpen, Left(s, PosClose), ",")
      Loop
    Else
      Exit Do
    End If
    PosOpen = InStr(PosClose + 1, s, "(")
  Loop
  ReplaceCommaInParen = Application.Trim(s)
End Function

A section of my sample data & results:

jeffdolton_3.xlsm
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Description	Product	Qty	Category	Unit Price	Product	Qty	Category	Unit Price	Product	Qty	Category	Unit Price	Product	Qty	Category	Unit Price	Product	Qty	Category	Unit Price
2	Free Tea	Free Tea	1
3	Free Coffee	Free Coffee	1
4	2 x Bacon Bap	Bacon Bap	2
5	2 x Coffee (Instant, OWN CUP), Coffee (Instant, Paper Cup), Tea (Paper Cup)	Coffee (Instant, OWN CUP)	2			Coffee (Instant, Paper Cup)	1			Tea (Paper Cup)	1
6	Cuppa Soup, Coffee (Instant, Paper Cup)	Cuppa Soup	1			Coffee (Instant, Paper Cup)	1
7	2 x Bacon Bap, Cheesy Potato Wedges, 2 x BBQ Hot Dog - 2 sausages, 2 x Bottled Water, 2 x Coffee (Filter, Paper Cup)	Bacon Bap	2			Cheesy Potato Wedges	1			BBQ Hot Dog - 2 sausages	2			Bottled Water	2			Coffee (Filter, Paper Cup)	2
8	3 x Coffee (Instant, Paper Cup)	Coffee (Instant, Paper Cup)	3
9	Bottled Water	Bottled Water	1
10	2 x Bacon Bap	Bacon Bap	2
11	2 x Bacon Bap, 3 x Hot Chocolate (Paper Cup)	Bacon Bap	2			Hot Chocolate (Paper Cup)	3
12	2 x Bacon Bap	Bacon Bap	2
13	Bacon Bap	Bacon Bap	1
14	2 x Bacon Bap, Coffee (Filter, Paper Cup)	Bacon Bap	2			Coffee (Filter, Paper Cup)	1
15	2 x Bacon Bap, Tea (Paper Cup)	Bacon Bap	2			Tea (Paper Cup)	1
16	Coffee (Instant, OWN CUP), Sweets in Packets	Coffee (Instant, OWN CUP)	1			Sweets in Packets	1
17	Coffee (Filter, Paper Cup, Cold), 2 x Choc bars OLD STOCK	Coffee (Filter, Paper Cup, Cold)	1			Choc bars OLD STOCK	2
18	Coffee (Filter, Paper Cup, Hot), Choc bars OLD STOCK	Coffee (Filter, Paper Cup, Hot)	1			Choc bars OLD STOCK	1
19	Bacon Bap, Coffee (Filter, Paper Cup)	Bacon Bap	1			Coffee (Filter, Paper Cup)	1
20	Cheesy Potato Wedges, 2 x BBQ Hot Dog - 2 sausages, Choc bars OLD STOCK	Cheesy Potato Wedges	1			BBQ Hot Dog - 2 sausages	2			Choc bars OLD STOCK	1
21	Bacon Bap, Cheesy Potato Wedges, BBQ Hot Dog - 2 sausages	Bacon Bap	1			Cheesy Potato Wedges	1			BBQ Hot Dog - 2 sausages	1
Receipts Output

 

[jeffdolton]

See how this goes, addressing both of the above.
The code allows for a maximum of 50 products per line but that can be altered if more could exist.

Sub Split_Transactions()
  Dim a As Variant, b As Variant
  Dim i As Long, maxCols As Long, PosCom As Long, Posx As Long, Qty As Long, col As Long
  Dim s As String, sTemp As String, sProd As String

  a = Range("L2", Range("L" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 200)
  For i = 1 To UBound(a)
    s = a(i, 1)
    col = -3
    sTemp = ReplaceCommaInParen(s) & ","
    Do Until Len(sTemp) = 0
      PosCom = InStr(1, sTemp, ",")
      sProd = Left(s, PosCom - 1)
      Posx = InStr(1, sProd, " x ")
      If Posx = 0 Then
        Qty = 1
      Else
        Qty = Left(sProd, Posx)
        Posx = Posx + 2
      End If
      col = col + 4
      If col > maxCols Then maxCols = maxCols + 4
      b(i, col) = Trim(Mid(sProd, Posx + 1))
      b(i, col + 1) = Qty
      sTemp = Trim(Mid(sTemp, PosCom + 1))
      s = Trim(Mid(s, PosCom + 1))
    Loop
  Next i
  With Range("M2").Resize(UBound(b), maxCols)
    .Value = b
    For i = 1 To maxCols Step 4
      .Cells(0, i).Resize(, 4).Value = Array("Product", "Qty", "Category", "Unit Price")
    Next i
    .EntireColumn.AutoFit
  End With
End Sub

Function ReplaceCommaInParen(ByVal s As String) As String
  Dim posStart As Long, PosOpen As Long, PosClose As Long, PosComma As Long

  PosOpen = InStr(posStart + 1, s, "(")
  Do Until PosOpen = 0
    PosClose = InStr(PosOpen, s, ")")
    If PosClose > PosOpen Then
      PosComma = InStr(PosOpen, Left(s, PosClose), ",")
      Do Until PosComma = 0
        Mid(s, PosComma, 1) = "^"
        PosOpen = PosComma
        PosComma = InStr(PosOpen, Left(s, PosClose), ",")
      Loop
    Else
      Exit Do
    End If
    PosOpen = InStr(PosClose + 1, s, "(")
  Loop
  ReplaceCommaInParen = Application.Trim(s)
End Function

A section of my sample data & results:

jeffdolton_3.xlsm
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Description	Product	Qty	Category	Unit Price	Product	Qty	Category	Unit Price	Product	Qty	Category	Unit Price	Product	Qty	Category	Unit Price	Product	Qty	Category	Unit Price
2	Free Tea	Free Tea	1
3	Free Coffee	Free Coffee	1
4	2 x Bacon Bap	Bacon Bap	2
5	2 x Coffee (Instant, OWN CUP), Coffee (Instant, Paper Cup), Tea (Paper Cup)	Coffee (Instant, OWN CUP)	2			Coffee (Instant, Paper Cup)	1			Tea (Paper Cup)	1
6	Cuppa Soup, Coffee (Instant, Paper Cup)	Cuppa Soup	1			Coffee (Instant, Paper Cup)	1
7	2 x Bacon Bap, Cheesy Potato Wedges, 2 x BBQ Hot Dog - 2 sausages, 2 x Bottled Water, 2 x Coffee (Filter, Paper Cup)	Bacon Bap	2			Cheesy Potato Wedges	1			BBQ Hot Dog - 2 sausages	2			Bottled Water	2			Coffee (Filter, Paper Cup)	2
8	3 x Coffee (Instant, Paper Cup)	Coffee (Instant, Paper Cup)	3
9	Bottled Water	Bottled Water	1
10	2 x Bacon Bap	Bacon Bap	2
11	2 x Bacon Bap, 3 x Hot Chocolate (Paper Cup)	Bacon Bap	2			Hot Chocolate (Paper Cup)	3
12	2 x Bacon Bap	Bacon Bap	2
13	Bacon Bap	Bacon Bap	1
14	2 x Bacon Bap, Coffee (Filter, Paper Cup)	Bacon Bap	2			Coffee (Filter, Paper Cup)	1
15	2 x Bacon Bap, Tea (Paper Cup)	Bacon Bap	2			Tea (Paper Cup)	1
16	Coffee (Instant, OWN CUP), Sweets in Packets	Coffee (Instant, OWN CUP)	1			Sweets in Packets	1
17	Coffee (Filter, Paper Cup, Cold), 2 x Choc bars OLD STOCK	Coffee (Filter, Paper Cup, Cold)	1			Choc bars OLD STOCK	2
18	Coffee (Filter, Paper Cup, Hot), Choc bars OLD STOCK	Coffee (Filter, Paper Cup, Hot)	1			Choc bars OLD STOCK	1
19	Bacon Bap, Coffee (Filter, Paper Cup)	Bacon Bap	1			Coffee (Filter, Paper Cup)	1
20	Cheesy Potato Wedges, 2 x BBQ Hot Dog - 2 sausages, Choc bars OLD STOCK	Cheesy Potato Wedges	1			BBQ Hot Dog - 2 sausages	2			Choc bars OLD STOCK	1
21	Bacon Bap, Cheesy Potato Wedges, BBQ Hot Dog - 2 sausages	Bacon Bap	1			Cheesy Potato Wedges	1			BBQ Hot Dog - 2 sausages	1
Receipts Output

That is fantastic and only took moments to run, thanks you so much.

 

[Peter_SSs]

That is fantastic and only took moments to run, thanks you so much.

You're welcome. It was an interesting exercise.