Of course, you may want to not insert/delete the entire original row, depending on what is elsewhere
on the sheet.....Change

Cells(myRow + 1, myCol).EntireRow.Insert

to

Cells(myRow + 1, myCol).Insert Shift:=xlDown

and change

Cells(myRow, myCol).EntireRow.Delete

to

Cells(myRow, myCol).Delete Shift:=xlUp



HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
JR,

Definitely need a macro for that one ;-)

Sub JRTextToRowsBelow()
Dim myRow As Long
Dim mySpl As Variant
Dim iCount As Integer
Dim myCol As Integer
Dim myDelim As String

myCol = 3
myDelim = "||"

For myRow = Cells(Rows.Count, myCol).End(xlUp).Row To 1 Step -1
If InStr(1, Cells(myRow, myCol).Value, myDelim) 0 Then
mySpl = Split(Cells(myRow, myCol).Value, myDelim)
For iCount = UBound(mySpl) To LBound(mySpl) Step -1
Cells(myRow + 1, myCol).EntireRow.Insert
Cells(myRow + 1, myCol).Value = mySpl(iCount)
Next iCount
Cells(myRow, myCol).EntireRow.Delete
End If
Next myRow
End Sub

HTH,
Bernie
MS Excel MVP


"JR" wrote in message
oups.com...
One other question. What if I wanted to take those values and
separate them on new excel rows instead of columns. So if the data in
column C was:

abc||cde||123

It would insert three new rows underneath and put:

abc
cde
123

One in each of the C column in the new rows and perhaps blank out the
abc||cde||123 value. Doing this all the way down the spreadsheet.

On Feb 6, 11:17 am, Dave Peterson wrote:
Actually, you can use the dialog to tell excel where to place the parsed data.

On the "step 3 of 3" wizard, you'll see a Destination box in the middle right
hand side.





Bernie Deitrick wrote:

JR,

Using Text to Columns manually is OK, if you want the first column to be column C. But since
you
want the first column to be F, you need a macro:

Sub JRSplit()

With Columns("C:C")
.TextToColumns Destination:=Range("F1"), _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
OtherChar:="|"
End With
End Sub

This assumes that every cell in column C with a | needs to be split....

HTH,
Bernie
MS Excel MVP

"JR" wrote in message
roups.com...
I have a particular cell in Excel that has multiple values defined in
it and separated by a delimiter. Was wondering if someone could help
me out with a macro that could parse this field down the spreadsheet
and separate those values into new columns in the spreadsheet.

So if Column C had:

abc||cde||123

I would like columns F, G, and H to have:
F would be abc
G would be cde
H would be 123

If Column C had
123||345||567||098
F would have 123
G would have 345
H would have 567
I would have 098

The number of values in C is not static and can vary.

Thanks.

JR

--

Dave Peterson- Hide quoted text -

- Show quoted text -