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

DataText to columns
Specify your own separator and tell Excel to treat multiple separators as one

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JR" wrote in message oups.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
|

Have a look at "Text to columns" on the Data menu.

"JR" wrote:

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

Select the column
Data|Text to columns
delimited
and follow the wizard from there.

JR wrote:

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

Go to Data and then Text to Columns. You can then separate by delimiter

"JR" wrote:

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

The solution is Text to Columns:

Copy Col C to Column F, then
use Data, Text to Columns, delimiter,
choose your delimiter, if you really have
two pipe symbols then check the option
to ignore duplicate delimiters on the next dialog


---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JR" wrote in message oups.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

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
oups.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

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
oups.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

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
oups.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

You know, I never noticed that little box before.... Thanks, Dave.

Bernie


"Dave Peterson" wrote in message
...
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.

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 -

I'd still use data|Text to columns.

But then I'd select the range
edit|copy
and select a non-overlapping range (on a new sheet)
and
edit|paste special|click Transpose.

and finally cut and paste to where I really wanted it.

JR wrote:

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 -

--

Dave Peterson

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 -

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 -