Extracting Comma Seperated Data - Desperate for help

[Rmcnaught]

I have been searching for days and everyone appears to be stumped. I probably should have just started entering the data.

Again, I must turn to the Excel Power Users with another dilemna. In the attached XLS I have 4 columns of data. My problem is extracting the data in columns B, C & D. I need to extract each color type to it's own individual cell. Example: I need 131A-Dark Gray placed in cell A1, 133-Blue placed in cell A2, 451-Burgundy placed in A3, etc, etc. The Data from columns C & D would also be placed in it's own row in the same way. Also as you can see each cell doesn't contain the same number of entries. This is for a project that is almost complete except for this data. I have tried to obtain this data for 3 days now.

Is there a way with a formula I can extract the individual color options for each item into a new sheet or am I going to have to do these entries manually, Yikes, I hope not I have 275 items. Someone suggested a Pivot Table and I have played with that for a whole day without any success. I feel like an idiot!!!!! I know that someone out there must know something

I now turn this over to all the brains.

Rich

Here is some additional information that may help. Each one of the color options in the cell is seperated by a comma so is there a way to get the data to the left and between the commas?

[compariniaa]

i have a semi-manual solution for you. i'm headed off to lunch, otherwise i'd try putting it all in code for you. the macro recorder may be of some use, however.

first copy all the cells with colors in column b. paste it into an empty column (doesn't matter where). then click DATA --> TEXT TO COLUMNS.

then a wizard will pop up. on the first screen, select "delimited". on the next screen check only the "comma" box. ignore the third screen (changing it doesn't do anything useful for this situation). and click finish. now you have each color in a different column.

to get it all in one column you'll need to transpose it

[Jacob Hilderbrand]

Use Data | Text To Columns.

• Select the data, B2:B5 for example
• Select Data | Text To Columns.
• Deliminted, Next
• Comma, Next
• Select the destination cell to put the values in E2 for example.
• Finish

[fixo]

Just snip of my poor code only
Hth
[VBA]
Sub Separate()
Dim str, str1 As String
Dim pos, cnt As Long
Dim rng As Range
Dim myVar() As String ''for debug only
On Error GoTo WhatA
With ActiveSheet.Range("C2:C5")
Set rng = .Cells(3, 1) ''i.e. to read 'C4' Cell
str = rng.Value
cnt = 0
Do
pos = (InStr(1, str, ","))
str1 = Left(str, pos - 1)
ReDim Preserve myVar(cnt)
myVar(cnt) = str1
cnt = cnt + 1
MsgBox "Extracted: " & str1
str = Right(str, Len(str) - pos)
MsgBox "Cutted string: " & str
Loop While (InStr(1, str, ",")) <> 0
If Len(str) <> 0 Then
ReDim Preserve myVar(cnt)
myVar(cnt) = str
End If
End With
WhatA:
End Sub
[/VBA]

[compariniaa]

here, this should do it:
[vba]Sub Colors()
'based off http://www.j-walk.com/ss/excel/tips/tip93.htm
Dim txt As String
Dim x As Variant
Dim i As Long
Dim r As Long
Dim row As Long
Dim c As Long

c = InputBox("Column Number of Color")
For row = 2 To Cells(65536, c).End(xlUp).row
txt = Cells(row, c).Value
x = Split(txt, ",")
r = Cells(63336, 10).End(xlUp).row + 1
For i = 0 To UBound(x)
Debug.Print x(i)
Cells(r, 10).Value = x(i)
r = r + 1
Next i
Next row
End Sub[/vba]

when the input box pops up, put in the column number of the area you'd like to split up, so if you wanted to list all the colors for column B, put in 2 when the input box pops up. it will list all the colors in rows starting from J2 Down

[mdmackillop]

Hi Rich,
Welcome to VBAX
What you need is the Split function
Regards
MD

[compariniaa]

wow. well done md

[mdmackillop]

He struggled for so long, I felt sorry for him!

[compariniaa]

I wonder if he has seen it yet

[fixo]

Here is my second attempt

[VBA]
Sub Separate()

Dim vStr, tStr As String
Dim rng As Range
Dim cel As Range
Dim pos, nRows, rRows, rCols, i, j As Long
Application.ScreenUpdating = False
Set rng = ActiveSheet.Range("B25")

With rng

rRows = .Rows.Count
rCols = .Columns.Count

For j = 1 To rCols
nRows = rRows + 1
For i = 1 To rRows
Set cel = .Cells(i, j)
vStr = cel.Value

Do While (InStr(1, vStr, ",")) <> 0

pos = (InStr(1, vStr, ","))
tStr = Left(vStr, pos - 1)
.Cells(nRows, j).Value = tStr
nRows = nRows + 1

vStr = Right(vStr, Len(vStr) - pos)

Loop

If Len(vStr) <> 0 Then
.Cells(nRows, j).Value = vStr
End If
nRows = nRows + 1
Next
nRows = nRows + 1
Next

End With

Application.ScreenUpdating = True

End Sub
[/VBA]

[mdmackillop]

Hi F
Welcome to VBAX. Its great to see new members come staight in with solutions.
When you post code, please select it and click the VBA button. This will do the formatting as Post 4.
With regard to this question, have a look at the Split function. You can do this with Instr, but for repeated separators, Split does this automatically.
Regards
MD

[fixo]

Hi F
Welcome to VBAX. Its great to see new members come staight in with solutions.
When you post code, please select it and click the VBA button. This will do the formatting as Post 4.
With regard to this question, have a look at the Split function. You can do this with Instr, but for repeated separators, Split does this automatically.
Regards
MD

Hi MD,
thanks for explanation,
[VBA]
Regards
[/VBA]