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.

 

[maabadi]

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

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]

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]

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

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]

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'.

Date	Time	Receipt number	VAT (0.0%)	Total	Fee	Net amount	Payment method	Card Schema	Last Digits	Staff	Description
19/12/2020​	12:11:57​	1712​	£0.00​	£1.90​	£0.00​	£1.90​	Cash			FPSC Bar	Free Tea
19/12/2020​	14:39:35​	1740​	£0.00​	£0.00​	£0.00​	£0.00​	Cash			FPSC Bar	Free Coffee
19/12/2020​	09:24:16​	1679​	£0.00​	£5.00​	£0.09​	£4.91​	Chip	MASTERCARD	8715	FPSC Bar	2 x Bacon Bap
17/12/2020​	15:36:04​	1669​	£0.00​	£3.40​	£0.06​	£3.34​	Contactless	MASTERCARD	2417	FPSC Bar	2 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​	Contactless	VISA	8039	FPSC Bar	Cuppa Soup, Coffee (Instant, Paper Cup)
19/12/2020​	09:16:05​	1671​	£0.00​	£15.75​	£0.28​	£15.47​	Contactless	VISA	0328	FPSC Bar	2 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​	Contactless	VISA	4039	FPSC Bar	3 x Coffee (Instant, Paper Cup)
19/12/2020​	09:18:45​	1673​	£0.00​	£1.00​	£0.02​	£0.98​	Contactless	VISA	9973	FPSC Bar	Bottled Water

 

[jeffdolton]

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

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]

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]

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]

Try this:

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]

Try this:

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.