Text to Rows?

  • Thread starter Thread starter RicardoE
  • Start date Start date
R

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.
 
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)
 
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
 
hi
you gave do indication about how much data you working with so
how about text to columns then copy pastspecial transpose?

regards
FSt1
 
Back
Top