creating a list from a column of semicolon

[instauratio]

I have one column that contains 1000 values. The problem is they do not
occupy one value per one cell with 1000 rows.

Some rows/cells have 10 to 15 values, each separated by a semi colon.

How can I sort or filter the entire column and separate each value into it's
own unique cell in one column?

[bj]

I would suggest using <data><Text to columns> <delimited with ;>
after doing this I would use <edit><goto><special><last cell> to identify
the highest column with data.
Sequencially Back to column B, I would sort these columns Cut and paste the
results to the bottom of column A.

"instauratio" wrote:

> I have one column that contains 1000 values. The problem is they do not
> occupy one value per one cell with 1000 rows.
>
> Some rows/cells have 10 to 15 values, each separated by a semi colon.
>
> How can I sort or filter the entire column and separate each value into it's
> own unique cell in one column?

[Martin P]

Copy to Word. Convert Table to Text (separate text with paragraph marks). Use
Edit, Replace to replace semi-colons with paragraph marks and then double
paragraph marks with single paragraph marks. Copy back to Excel.

"instauratio" wrote:

> I have one column that contains 1000 values. The problem is they do not
> occupy one value per one cell with 1000 rows.
>
> Some rows/cells have 10 to 15 values, each separated by a semi colon.
>
> How can I sort or filter the entire column and separate each value into it's
> own unique cell in one column?

[Dave Peterson]

This worked ok for me:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim destCell As Range
Dim NumberOfRows As Long

Dim myRng As Range
Dim myCell As Range
Dim mySplit As Variant

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set destCell = newWks.Range("a1")

For Each myCell In myRng.Cells
mySplit = Split97(myCell.Value, ";")
'or if xl2k and higher
'mySplit = Split(myCell.Value, ";")
NumberOfRows = UBound(mySplit) - LBound(mySplit) + 1
destCell.Resize(NumberOfRows, 1).Value _
= Application.Transpose(mySplit)

Set destCell = destCell.Offset(NumberOfRows, 0)
Next myCell

End Sub
Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

Split was added in xl2k. If you're using xl97, use Tom's split97. If you and
your users are all at xl2k or higher, you can delete that function completely.


If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


instauratio wrote:
>
> I have one column that contains 1000 values. The problem is they do not
> occupy one value per one cell with 1000 rows.
>
> Some rows/cells have 10 to 15 values, each separated by a semi colon.
>
> How can I sort or filter the entire column and separate each value into it's
> own unique cell in one column?

--

Dave Peterson