Separate values in cell by delimiter

[JR]

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

 

[Niek Otten]

Data>Text to columns
Specify your own separator and tell Excel to treat multiple separators as one

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Guest]

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

 

[Dave Peterson]

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

 

[Guest]

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

 

[David McRitchie]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[Dave Peterson]

Actually, you can use the dialog to tell excel where to place the parsed data.

 

[Bernie Deitrick]

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

Bernie

 

[JR]

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.

 

[Dave Peterson]

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.

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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