Functions to separate a string separated by commas

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,513
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
LMNOPQRSTUVWXYZAAABACADAEAF
1DescriptionProductQtyCategoryUnit PriceProductQtyCategoryUnit PriceProductQtyCategoryUnit PriceProductQtyCategoryUnit PriceProductQtyCategoryUnit Price
2Free TeaFree Tea1
3Free CoffeeFree Coffee1
42 x Bacon BapBacon Bap2
52 x Coffee (Instant, OWN CUP), Coffee (Instant, Paper Cup), Tea (Paper Cup)Coffee (Instant, OWN CUP)2Coffee (Instant, Paper Cup)1Tea (Paper Cup)1
6Cuppa Soup, Coffee (Instant, Paper Cup)Cuppa Soup1Coffee (Instant, Paper Cup)1
72 x Bacon Bap, Cheesy Potato Wedges, 2 x BBQ Hot Dog - 2 sausages, 2 x Bottled Water, 2 x Coffee (Filter, Paper Cup)Bacon Bap2Cheesy Potato Wedges1BBQ Hot Dog - 2 sausages2Bottled Water2Coffee (Filter, Paper Cup)2
83 x Coffee (Instant, Paper Cup)Coffee (Instant, Paper Cup)3
9Bottled WaterBottled Water1
102 x Bacon BapBacon Bap2
112 x Bacon Bap, 3 x Hot Chocolate (Paper Cup)Bacon Bap2Hot Chocolate (Paper Cup)3
122 x Bacon BapBacon Bap2
13Bacon BapBacon Bap1
142 x Bacon Bap, Coffee (Filter, Paper Cup)Bacon Bap2Coffee (Filter, Paper Cup)1
152 x Bacon Bap, Tea (Paper Cup)Bacon Bap2Tea (Paper Cup)1
16Coffee (Instant, OWN CUP), Sweets in PacketsCoffee (Instant, OWN CUP)1Sweets in Packets1
17Coffee (Filter, Paper Cup, Cold), 2 x Choc bars OLD STOCKCoffee (Filter, Paper Cup, Cold)1Choc bars OLD STOCK2
18Coffee (Filter, Paper Cup, Hot), Choc bars OLD STOCKCoffee (Filter, Paper Cup, Hot)1Choc bars OLD STOCK1
19Bacon Bap, Coffee (Filter, Paper Cup)Bacon Bap1Coffee (Filter, Paper Cup)1
20Cheesy Potato Wedges, 2 x BBQ Hot Dog - 2 sausages, Choc bars OLD STOCKCheesy Potato Wedges1BBQ Hot Dog - 2 sausages2Choc bars OLD STOCK1
21Bacon Bap, Cheesy Potato Wedges, BBQ Hot Dog - 2 sausagesBacon Bap1Cheesy Potato Wedges1BBQ Hot Dog - 2 sausages1
Receipts Output
 
Last edited:
Solution

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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
LMNOPQRSTUVWXYZAAABACADAEAF
1DescriptionProductQtyCategoryUnit PriceProductQtyCategoryUnit PriceProductQtyCategoryUnit PriceProductQtyCategoryUnit PriceProductQtyCategoryUnit Price
2Free TeaFree Tea1
3Free CoffeeFree Coffee1
42 x Bacon BapBacon Bap2
52 x Coffee (Instant, OWN CUP), Coffee (Instant, Paper Cup), Tea (Paper Cup)Coffee (Instant, OWN CUP)2Coffee (Instant, Paper Cup)1Tea (Paper Cup)1
6Cuppa Soup, Coffee (Instant, Paper Cup)Cuppa Soup1Coffee (Instant, Paper Cup)1
72 x Bacon Bap, Cheesy Potato Wedges, 2 x BBQ Hot Dog - 2 sausages, 2 x Bottled Water, 2 x Coffee (Filter, Paper Cup)Bacon Bap2Cheesy Potato Wedges1BBQ Hot Dog - 2 sausages2Bottled Water2Coffee (Filter, Paper Cup)2
83 x Coffee (Instant, Paper Cup)Coffee (Instant, Paper Cup)3
9Bottled WaterBottled Water1
102 x Bacon BapBacon Bap2
112 x Bacon Bap, 3 x Hot Chocolate (Paper Cup)Bacon Bap2Hot Chocolate (Paper Cup)3
122 x Bacon BapBacon Bap2
13Bacon BapBacon Bap1
142 x Bacon Bap, Coffee (Filter, Paper Cup)Bacon Bap2Coffee (Filter, Paper Cup)1
152 x Bacon Bap, Tea (Paper Cup)Bacon Bap2Tea (Paper Cup)1
16Coffee (Instant, OWN CUP), Sweets in PacketsCoffee (Instant, OWN CUP)1Sweets in Packets1
17Coffee (Filter, Paper Cup, Cold), 2 x Choc bars OLD STOCKCoffee (Filter, Paper Cup, Cold)1Choc bars OLD STOCK2
18Coffee (Filter, Paper Cup, Hot), Choc bars OLD STOCKCoffee (Filter, Paper Cup, Hot)1Choc bars OLD STOCK1
19Bacon Bap, Coffee (Filter, Paper Cup)Bacon Bap1Coffee (Filter, Paper Cup)1
20Cheesy Potato Wedges, 2 x BBQ Hot Dog - 2 sausages, Choc bars OLD STOCKCheesy Potato Wedges1BBQ Hot Dog - 2 sausages2Choc bars OLD STOCK1
21Bacon Bap, Cheesy Potato Wedges, BBQ Hot Dog - 2 sausagesBacon Bap1Cheesy Potato Wedges1BBQ Hot Dog - 2 sausages1
Receipts Output
That is fantastic and only took moments to run, thanks you so much.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,022
Messages
5,575,625
Members
412,680
Latest member
TSpan
Top