Automating separation of data.

Ryan1996

Board Regular
Joined
Jun 4, 2018
Messages
55
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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.............
 
Upvote 0
If the list values are always separated with ", " then it's a trivial task in VBA:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,280
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top