Automating separation of data.

[Ryan1996]

No idea if this is even possible or how are it is but here goes:

Lets say i have a few columns with data in as below:
A B C D E
1 A1 B1 C1 D1 E1
2 A2 B2 C2 D2 E2
3 A3 B3 C3 D3 E3
4 A4 B4 C4 D4 E4
5 A5 B5 C5 D5 E5

Now lets say the information in column C is as below:
A B C D E
1 A1 B1 INC12345 D1 E1
2 A2 B2 INC13245 D2 E2
3 A3 B3 INC21354 D3 E3
4 A4 B4 INC52351 D4 E4
5 A5 B5 INC15324 D5 E5

However sometimes the data could be more than one, like this:
A B C D E
1 A1 B1 INC12345 D1 E1
2 A2 B2 INC13245 D2 E2
3 A3 B3 INC21354, INC84732 D3 E3
4 A4 B4 INC52351, INC98712, INC60939 D4 E4
5 A5 B5 INC15324 D5 E5

I need to be able to copy column C and paste as a list in another column/sheet.
Therefore the data as above would look like this:
INC12345
INC13245
INC21354
INC84732
INC52351
INC98712
INC60939
INC15324

I don't really care which order it places them as long as they are below each other in new rows.

Is this possible? Can anyone explain how i can do this?

Thanks in advance!

 

[oldbrewer]

Now lets say the information in column C is as below:
A B C D E
1 A1 B1 INC12345 D1 E1
2 A2 B2 INC13245 D2 E2
3 A3 B3 INC21354 D3 E3
4 A4 B4 INC52351 D4 E4
5 A5 B5 INC15324 D5 E5

but column C starts C1.............

 

[wideboydixon]

If the list values are always separated with ", " then it's a trivial task in VBA:

Public Sub SeparateData()

Dim lastRow As Long
Dim thisRow As Long
Dim nextRow As Long
Dim valueList As Variant
Dim i As Long
Dim targetSheet As Worksheet

' Set the target worksheet
Set targetSheet = Sheets("Sheet3")

' Set the next row on the target sheet
nextRow = 1

' Find the last row of data in column C
lastRow = Cells(Rows.Count, "C").End(xlUp).Row

' Process all rows
For thisRow = 1 To lastRow
    ' Split the values in column C using ", " as a separator
    valueList = Split(Cells(thisRow, "C").Value, ", ")
    
    ' Process all the values found
    For i = 0 To UBound(valueList)
        ' Add the value to the target sheet and move to the next row
        targetSheet.Cells(nextRow, "A").Value = valueList(i)
        nextRow = nextRow + 1
    Next i
Next thisRow

End Sub

WBD

 

[Ryan1996]

**** that was fast. Perfect, i'll change the data to fit with the setup of my sheet. Thanks a lot!