Text to Rows?

[RicardoE]

Hello,

I can't find info on how to split up the comma-delimited contents of a cell
into multiple rows rather than columns. Is this possible? Could a Macro do
it? If so, any samples?

For example, my cell contents currently are: "C346,C349,C362"
I would like to split them up as:

C346
C349
C362

Rather than into individual columns, as Text to Columns would do.

Thanks,

Ricky.

 

[Chip Pearson]

Here's some simple code that you can adapt for your specific needs.
Change SourceCell to the range containing the source, comma-delimited
text and change DestinationCell to the first cell where the split
apart text should be placed.

Sub TextToRows()
Dim N As Long
Dim V As Variant
Dim SourceCell As Range
Dim DestinationCell As Range
Set SourceCell = Range("A1") '<<<< CHANGE AS NEEDED
Set DestinationCell = Range("A2") '<<<< CHANGE AS NEEDED
V = Split(SourceCell, ",")
For N = LBound(V) To UBound(V)
DestinationCell.Offset(N, 0).Value = V(N)
Next N
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gary''s Student]

Two ways:

1. use Text to Columns and then paste/special/transpose the resulting row
onto a column

2. Select a cell and run this macro. It uses the cells immediately below to
store values:

Sub splitum()
v = Selection.Value
s = Split(v, ",")
For i = 0 To UBound(s)
Selection.Offset(i + 1, 0).Value = s(i)
Next
End Sub

 

[FSt1]

hi
you gave do indication about how much data you working with so
how about text to columns then copy pastspecial transpose?

regards
FSt1