splitting cells based on commas

aaronnechama

New Member
Joined
Mar 11, 2009
Messages
6
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
Joined
Sep 6, 2002
Messages
9,118
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
Joined
Jan 1, 2009
Messages
3,468
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

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

Some videos you may like

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...
  • Listbox Header
    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...
Top