Converting data into multiple rows
 

I have a spreadsheet of raw data with several columns. One of the columns
contains multiple market segments separated by commas (Chemicals, Specialty
Chemicals, Polymers, etc). I want to break out each row into multiple rows
based on however many market segments there are. For example, one row of data
may have 6 market segments. I want to convert that row of data into 6 rows of
data, one per market segment. Is there an easy way to do this? I have 1000+
rows of data, so doing this manually is extremely painful.

Converting data into multiple rows
 

Kathleen,

First ensure you have sufficient empty columns to the right of your column
of data to split the data into then:

Data|Text to columns - delimited - next - select comma - finish


Mike

"Kathleen_TX" wrote:

I have a spreadsheet of raw data with several columns. One of the columns
contains multiple market segments separated by commas (Chemicals, Specialty
Chemicals, Polymers, etc). I want to break out each row into multiple rows
based on however many market segments there are. For example, one row of data
may have 6 market segments. I want to convert that row of data into 6 rows of
data, one per market segment. Is there an easy way to do this? I have 1000+
rows of data, so doing this manually is extremely painful.

Converting data into multiple rows
 

Thanks Mike. I tried that, but that's not exactly what I'm trying to do. I
want to break out the entire row of data into multiple rows.

"Mike H" wrote:

Kathleen,

First ensure you have sufficient empty columns to the right of your column
of data to split the data into then:

Data|Text to columns - delimited - next - select comma - finish


Mike

"Kathleen_TX" wrote:

I have a spreadsheet of raw data with several columns. One of the columns
contains multiple market segments separated by commas (Chemicals, Specialty
Chemicals, Polymers, etc). I want to break out each row into multiple rows
based on however many market segments there are. For example, one row of data
may have 6 market segments. I want to convert that row of data into 6 rows of
data, one per market segment. Is there an easy way to do this? I have 1000+
rows of data, so doing this manually is extremely painful.

Converting data into multiple rows
 

this sub checks the contents of column C and adds new rows for each item if
theres a cell with more than one item comma separated

Option Explicit
Sub breakup()
Dim cell As Range
Dim rownum As Long
Dim data As Variant
Dim index As Long
'insert from bottom to top
'get the last row
rownum = Range("C1").End(xlDown).Row
For rownum = rownum To 2 Step -1
If InStr(Cells(rownum, "C").Value, ",") 0 Then
data = Split(Cells(rownum, "C"), ",")
For index = 0 To UBound(data, 1)
Rows(rownum + 1).Insert
Cells(rownum + 1, "C") = data(index)
Next
Rows(rownum).Delete
End If
Next
End Sub

"Kathleen_TX" wrote in message
...
I have a spreadsheet of raw data with several columns. One of the columns
contains multiple market segments separated by commas (Chemicals,
Specialty
Chemicals, Polymers, etc). I want to break out each row into multiple rows
based on however many market segments there are. For example, one row of
data
may have 6 market segments. I want to convert that row of data into 6 rows
of
data, one per market segment. Is there an easy way to do this? I have
1000+
rows of data, so doing this manually is extremely painful.