# splitting cells based on commas

#### aaronnechama

##### New Member
How do I split a series of cells based on commas? There is a series of data in 2 consecutive cells, with the data in each cell corresponding to the data in the adjacent cell. I need that each pair should be lined up in separate columns. Here is the example:

I need to match up the SKU with the corresponding Quantity in the adjacent column. Any ideas would be greatly appreciated.

#### DonkeyOte

##### MrExcel MVP
Not entirely sure what your desired output is... using formulae perhaps something like:

Excel Workbook
GHIJKLMNOPQRS
2a,b,c,d1,1,2,14bcd121
3a,b1,12ab11
4s11s1
5apple,banana,carrot,donkey,elephant10,300,1,2,35applebananacarrotdonkeyelephant10300123
Sheet1

but you will need to first determine max no. of SKU's in any given line as this should dictate how many columns you setup for each section (SKU / Quantity), you can do this using by first inserting the formula provided in I and then running a MAX against this column.

You might want to think about VBA of course...

#### sanrv1f

##### MrExcel MVP
may be this

Code:
``````Option Explicit
Function joins(ByVal ref1 As String, ByVal ref2 As String) As String
Dim i As Integer, arr1, arr2
arr1 = Split(ref1, ",")
arr2 = Split(ref2, ",")
'joins = ""
For i = LBound(arr1) To Application.Min(UBound(arr1), UBound(arr2))
joins = Join(Array(joins, arr1(i), arr2(i)), ",")
Next
joins = Right(joins, Len(joins) - 1)
End Function``````
Excel Workbook
ABC
1a,b,c,d1,2,3,4a,1,b,2,c,3,d,4
Sheet1
Excel 2003
Cell Formulas
Range(s)Formula
C1=joins(A1,B1)

### Forum statistics

1,082,323
Messages
5,364,586
Members
400,808
Latest member
formulasataglance

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
• Complex Heat Map using conditional formatting
Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
• Conditional formatting
Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...