Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
JR JR is offline
external usenet poster
 
Posts: 8
Default Separate values in cell by delimiter

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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default Separate values in cell by delimiter

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
|


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 222
Default Separate values in cell by delimiter

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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Separate values in cell by delimiter

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default Separate values in cell by delimiter

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




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 903
Default Separate values in cell by delimiter

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



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Separate values in cell by delimiter

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



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Separate values in cell by delimiter

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



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Separate values in cell by delimiter

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
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Separate values in cell by delimiter

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.






  #11   Report Post  
Posted to microsoft.public.excel.misc
JR JR is offline
external usenet poster
 
Posts: 8
Default Separate values in cell by delimiter

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 -



  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Separate values in cell by delimiter

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
  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Separate values in cell by delimiter

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 -





  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Separate values in cell by delimiter

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 -







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct issues SteveDB1 Excel Worksheet Functions 25 June 3rd 09 04:58 PM
Return Matched Numeric Values across Rows Sam via OfficeKB.com Excel Worksheet Functions 2 January 3rd 07 12:03 AM
Find One Of Several Values Within A Cell Jim J. Excel Worksheet Functions 11 January 2nd 07 09:02 PM
Compiling macro based on cell values simonsmith Excel Discussion (Misc queries) 1 May 16th 06 08:31 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 03:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"