Functions to separate a string separated by commas

J

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
100
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.
 
Sort by date Sort by votes
jeffdolton said:
it worked perfectly but did take a long time to run though
Click to expand...

jeffdolton said:
each product and number is separated by two blank columns?
Click to expand...
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:
Upvote 0
Solution

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
Peter_SSs said:
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
Click to expand...
That is fantastic and only took moments to run, thanks you so much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
pivot table from columns with strings separated by commas
Replies
7
Views
1K
Fluff
Fluff
J
  • Solved
Removing a comma inside a parenthesis or parentheses
3 4 5
Replies
40
Views
5K
Peter_SSs
Peter_SSs
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
593
Peter_SSs
Peter_SSs
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top