Functions to separate a string separated by commas

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
66
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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
861
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Use this code. this code take data from column A and extract it to column D
also delete column A after finished extracting. if you don't want to delete column A at the last add ' symbol at first of last line before End Sub Line
VBA Code:
Sub SplitByCommaExample()

Dim MyArray() As String, MyString As String, I As Variant, N As Integer
Dim Lr As Long, Lr2 As Long, a As Long, b As Long, c As Long
Range("D2").Value = "Extract"
Lr = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To Lr

MyString = Range("A" & a).Value

MyArray = Split(MyString, ",")
Lr2 = Cells(Rows.Count, 4).End(xlUp).Row
For N = 0 To UBound(MyArray)
    Range("D" & N + 1 + Lr2).Value = Trim(MyArray(N))
Next N
Next a


Lr = Cells(Rows.Count, 4).End(xlUp).Row

For a = 4 To Lr
b = InStr(Range("D" & a).Value, "(")
c = InStr(Range("D" & a).Value, ")")
If b > 0 And c = 0 Then
Range("D" & a).Value = Range("D" & a).Value & ", " & Range("D" & a + 1).Value
Range("D" & a + 1).ClearContents
ElseIf Range("D" & a).Value = "" Then
Range("D" & a).Delete (XlDeleteShiftDirection.xlShiftUp)
End If
Next a
Range("A" & Lr).ClearContents
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,475
Office Version
  1. 365
Platform
  1. Windows
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)
Is this correct?
First part says leave the comma.
Second part says if that is too difficult then commas can stay.

Both say to leave commas in parentheses?

Instead of just one example, could we have 5 or 6 with the expected results and with XL2BB so that we can see the expected layout better and can easily copy the exact data to test with?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
861
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Use this code. this code take data from column A and extract it to column D
also delete column A after finished extracting. if you don't want to delete column A at the last add ' symbol at first of last line before End Sub Line
VBA Code:
Sub SplitByCommaExample()

Dim MyArray() As String, MyString As String, N As Integer
Dim Lr As Long, Lr2 As Long, a As Long, b As Long
Range("D2").Value = "Extract"
Lr = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To Lr

MyString = Range("A" & a).Value

MyArray = Split(MyString, ",")
Lr2 = Cells(Rows.Count, 4).End(xlUp).Row
For N = 0 To UBound(MyArray)
    Range("D" & N + 1 + Lr2).Value = Trim(MyArray(N))
Next N
Next a


Lr = Cells(Rows.Count, 4).End(xlUp).Row

For a = 3 To Lr
b = InStr(Range("D" & a).Value, "(")
c = InStr(Range("D" & a).Value, ")")
If b > 0 And c = 0 Then
Range("D" & a).Value = Range("D" & a).Value & ", " & Range("D" & a + 1).Value
Range("D" & a + 1).ClearContents
ElseIf Range("D" & a).Value = "" Then
Range("D" & a).Delete (XlDeleteShiftDirection.xlShiftUp)
End If
MyArray = Split(Range("D" & a).Value, "x ")
If UBound(MyArray) = 1 Then
Range("D" & a).Value = Trim(MyArray(1))
Range("E" & a).Value = Trim(MyArray(0))
Else
Range("E" & a).Value = 1
End If
Next a
Lr2 = Cells(Rows.Count, 4).End(xlUp).Row
Range("E" & Lr2 + 1 & ":E" & Lr).ClearContents
'Range("A2:A" & Lr).ClearContents
End Sub
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
66
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Is this correct?
First part says leave the comma.
Second part says if that is too difficult then commas can stay.

Both say to leave commas in parentheses?

Instead of just one example, could we have 5 or 6 with the expected results and with XL2BB so that we can see the expected layout better and can easily copy the exact data to test with?
Sorry, my mistake. should say leave comma in parentheses, but if it's too difficult then remove comma. Below is an EPOS extract. I want the Description separated into as many columns as there are purchases, two columns per purchase, one for the product and one for the number of items bought. So, the first row should show Free Tea 1 - that is two columns added to the right of 'Description'.. The 4th row would look like Coffee (Instant OWN Cup) 2 Coffee (Instant Paper Cup) 1 Tea (Paper Cup) 1 - that is six columns added to the right of 'Description'.

DateTimeReceipt numberVAT (0.0%)TotalFeeNet amountPayment methodCard SchemaLast DigitsStaffDescription
19/12/2020​
12:11:57​
1712​
£0.00​
£1.90​
£0.00​
£1.90​
CashFPSC BarFree Tea
19/12/2020​
14:39:35​
1740​
£0.00​
£0.00​
£0.00​
£0.00​
CashFPSC BarFree Coffee
19/12/2020​
09:24:16​
1679​
£0.00​
£5.00​
£0.09​
£4.91​
ChipMASTERCARD8715FPSC Bar2 x Bacon Bap
17/12/2020​
15:36:04​
1669​
£0.00​
£3.40​
£0.06​
£3.34​
ContactlessMASTERCARD2417FPSC Bar2 x Coffee (Instant, OWN CUP), Coffee (Instant, Paper Cup), Tea (Paper Cup)
17/12/2020​
15:38:41​
1670​
£0.00​
£2.00​
£0.04​
£1.96​
ContactlessVISA8039FPSC BarCuppa Soup, Coffee (Instant, Paper Cup)
19/12/2020​
09:16:05​
1671​
£0.00​
£15.75​
£0.28​
£15.47​
ContactlessVISA0328FPSC Bar2 x Bacon Bap, Cheesy Potato Wedges, 2 x BBQ Hot Dog - 2 sausages, 2 x Bottled Water, 2 x Coffee (Filter, Paper Cup)
19/12/2020​
09:17:47​
1672​
£0.00​
£3.00​
£0.05​
£2.95​
ContactlessVISA4039FPSC Bar3 x Coffee (Instant, Paper Cup)
19/12/2020​
09:18:45​
1673​
£0.00​
£1.00​
£0.02​
£0.98​
ContactlessVISA9973FPSC BarBottled Water
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
66
Office Version
  1. 2010
Platform
  1. Windows
Use this code. this code take data from column A and extract it to column D
also delete column A after finished extracting. if you don't want to delete column A at the last add ' symbol at first of last line before End Sub Line
VBA Code:
Sub SplitByCommaExample()

Dim MyArray() As String, MyString As String, N As Integer
Dim Lr As Long, Lr2 As Long, a As Long, b As Long
Range("D2").Value = "Extract"
Lr = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To Lr

MyString = Range("A" & a).Value

MyArray = Split(MyString, ",")
Lr2 = Cells(Rows.Count, 4).End(xlUp).Row
For N = 0 To UBound(MyArray)
    Range("D" & N + 1 + Lr2).Value = Trim(MyArray(N))
Next N
Next a


Lr = Cells(Rows.Count, 4).End(xlUp).Row

For a = 3 To Lr
b = InStr(Range("D" & a).Value, "(")
c = InStr(Range("D" & a).Value, ")")
If b > 0 And c = 0 Then
Range("D" & a).Value = Range("D" & a).Value & ", " & Range("D" & a + 1).Value
Range("D" & a + 1).ClearContents
ElseIf Range("D" & a).Value = "" Then
Range("D" & a).Delete (XlDeleteShiftDirection.xlShiftUp)
End If
MyArray = Split(Range("D" & a).Value, "x ")
If UBound(MyArray) = 1 Then
Range("D" & a).Value = Trim(MyArray(1))
Range("E" & a).Value = Trim(MyArray(0))
Else
Range("E" & a).Value = 1
End If
Next a
Lr2 = Cells(Rows.Count, 4).End(xlUp).Row
Range("E" & Lr2 + 1 & ":E" & Lr).ClearContents
'Range("A2:A" & Lr).ClearContents
End Sub
Many thanks for the code. I really like the way all the products are shown in a single column and will make my life far easier applying other excel functions to this. I have noted though that the code separates purchase options - these are shown inside parenthesis and are separated with a comma. Your code splits for instance Coffee (Instant, Paper Cup) into two rows - please see below. It's obvious the code is separating at each comma so how can the comma inside parentheses not be split out? Or perhaps there is code to remove the comma inside parentheses first so the result is Coffee (Instant Paper Cup). Thanks again.

Coffee (Instant
3​
Paper Cup)
1​
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
66
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Many thanks for the code. I really like the way all the products are shown in a single column and will make my life far easier applying other excel functions to this. I have noted though that the code separates purchase options - these are shown inside parenthesis and are separated with a comma. Your code splits for instance Coffee (Instant, Paper Cup) into two rows - please see below. It's obvious the code is separating at each comma so how can the comma inside parentheses not be split out? Or perhaps there is code to remove the comma inside parentheses first so the result is Coffee (Instant Paper Cup). Thanks again.

Coffee (Instant
3​
Paper Cup)
1​
Sorry, just realised this approach won't work. I need to have a third column showing 'Payment Type' - please see the table I posted earlier. Also I've noticed that the options in parentheses do remain with the product, such as:
Coffee (Instant, OWN CUP)
or
Tea (Paper Cup)
but one item was separated to another row
Coffee (Instant
3​
Paper Cup)
1​

Thanks.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
861
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
For me Working well. If you want all product in one row at multiple column, I can change it.
And what about item numbers, you want item numbers at the next column of each item.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
861
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
VBA Code:
Option Explicit

Sub SplitByCommaExample()

Dim MyArray() As String, MyString As String, N As Integer, j As Long
Dim Lr As Long, Lr2 As Long, a As Long, b As Long, c As Long, d As Long
Range("D2").Value = "Extract"
Lr = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To Lr

MyString = Range("A" & a).Value

MyArray = Split(MyString, ",")
Lr2 = Cells(Rows.Count, 4).End(xlUp).Row
For N = 0 To UBound(MyArray)
b = InStr(Trim(MyArray(N)), "(")
c = InStr(Trim(MyArray(N)), ")")
If b > 0 And c = 0 Then
MyArray(N) = Trim(MyArray(N)) & ", " & Trim(MyArray(N + 1))
MyArray(N + 1) = ""
End If
    Cells(a, N * 2 + 1 + 3).Value = Trim(MyArray(N))
    If N > d Then
    d = N
    End If
Next N
Next a

For j = 4 To 2 * (d + 1)
For a = 2 To Lr
If Cells(a, j) <> "" Then
MyArray = Split(Cells(a, j).Value, "x ")
If UBound(MyArray) = 1 Then
Cells(a, j).Value = Trim(MyArray(1))
Cells(a, j + 1).Value = Trim(MyArray(0))
ElseIf IsNumeric(Cells(a, j)) = True Then
Else
Cells(a, j + 1).Value = 1
End If
End If
Next a
Next j
For j = 4 To 2 * (d + 1)
For a = 2 To Lr
If Cells(a, j) = "" Then
Cells(a, j).Delete (XlDeleteShiftDirection.xlShiftToLeft)
End If
Next a
Next j
End Sub
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
66
Office Version
  1. 2010
Platform
  1. Windows
Try this:
VBA Code:
Option Explicit

Sub SplitByCommaExample()

Dim MyArray() As String, MyString As String, N As Integer, j As Long
Dim Lr As Long, Lr2 As Long, a As Long, b As Long, c As Long, d As Long
Range("D2").Value = "Extract"
Lr = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To Lr

MyString = Range("A" & a).Value

MyArray = Split(MyString, ",")
Lr2 = Cells(Rows.Count, 4).End(xlUp).Row
For N = 0 To UBound(MyArray)
b = InStr(Trim(MyArray(N)), "(")
c = InStr(Trim(MyArray(N)), ")")
If b > 0 And c = 0 Then
MyArray(N) = Trim(MyArray(N)) & ", " & Trim(MyArray(N + 1))
MyArray(N + 1) = ""
End If
    Cells(a, N * 2 + 1 + 3).Value = Trim(MyArray(N))
    If N > d Then
    d = N
    End If
Next N
Next a

For j = 4 To 2 * (d + 1)
For a = 2 To Lr
If Cells(a, j) <> "" Then
MyArray = Split(Cells(a, j).Value, "x ")
If UBound(MyArray) = 1 Then
Cells(a, j).Value = Trim(MyArray(1))
Cells(a, j + 1).Value = Trim(MyArray(0))
ElseIf IsNumeric(Cells(a, j)) = True Then
Else
Cells(a, j + 1).Value = 1
End If
End If
Next a
Next j
For j = 4 To 2 * (d + 1)
For a = 2 To Lr
If Cells(a, j) = "" Then
Cells(a, j).Delete (XlDeleteShiftDirection.xlShiftToLeft)
End If
Next a
Next j
End Sub
Thanks and so nearly there! I found one instance where the product skipped one column so that the products and number of products columns didn't line up - see 2nd row below.

Bacon Bap
2​
Coffee (Instant, OWN CUP)
2​
Coffee (Instant, Paper Cup)
1​
Cuppa Soup
1​
Coffee (Instant, Paper Cup)
1​
Bacon Bap
2​
Cheesy Potato Wedges
1​
BBQ Hot Dog - 2 sausages

Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,174
Messages
5,570,684
Members
412,336
Latest member
Tiffany927
Top