splitting cells based on commas

[aaronnechama]

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]

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

Excel Workbook
	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	a,b,c,d	1,1,2,1	4	a	b	c	d		1	1	2	1
3	a,b	1,1	2	a	b				1	1
4	s	1	1	s					1
5	apple,banana,carrot,donkey,elephant	10,300,1,2,3	5	apple	banana	carrot	donkey	elephant	10	300	1	2	3
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]

may be this

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
	A	B	C
1	a,b,c,d	1,2,3,4	a,1,b,2,c,3,d,4
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C1	=joins(A1,B1)

 

[DonkeyOte]

For ref. x-posted: http://www.excelforum.com/excel-pro...o-one-cell-needs-to-be-split.html#post2161823