Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pieces

Each cell in column A contains multiple entries separated by a comma and a
space (e.g., P7899, P7899.7, P9250, P9261). Ideally, I would like to use a
built-in function (versus a custom function if possible) that extracts the
contents of each cell in column A and puts the component pieces into separate
cells in column B. Using the previous example, one cell in column A would
become four cells in column B.

Thanks for the help.

Bob
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pieces

Put the pieces into separate cells with
Data Text to Columns Delimited Comma, Space
Name the array <arrayB.
Convert the array into a single column with this formula
=INDEX(arrayB,(ROWS($1:1)-1)/COLUMNS(arrayB)+1,
MOD(ROWS($1:1)-1,COLUMNS(arrayB))+1)
and copy down until you get #REF

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

Herbert,
Thanks for your help! Unfortunately, it appears that your formula only
worked for the first cell containing multiple entries. Afterwards, the
formula returned "0" (zero). Am I missing something?
Thanks again,
Bob


"Herbert Seidenberg" wrote:

Put the pieces into separate cells with
Data Text to Columns Delimited Comma, Space
Name the array <arrayB.
Convert the array into a single column with this formula
=INDEX(arrayB,(ROWS($1:1)-1)/COLUMNS(arrayB)+1,
MOD(ROWS($1:1)-1,COLUMNS(arrayB))+1)
and copy down until you get #REF


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

Here is my setup. Maybe we assumed different initial conditions.
A1 thru A5 has this arbitrary text data:
A531, A493, C941, D526
G988, G400, H552, B584
F542, C723, H958, G598
K384, H410, C993, H223
E378, A721, C642, E549
After Text to Columns, I get at A1 thru D5:
A531 A493 C941 D526
G988 G400 H552 B584
F542 C723 H958 G598
K384 H410 C993 H223
E378 A721 C642 E549
I named A1:D5 arrayB. Verify that the Name Box shows this.
The formula entered at A15 and dragged down to A34 gave this:
A531
A493
C941
D526
G988
G400
H552
B584
F542
C723
H958
G598
K384
H410
C993
H223
E378
A721
C642
E549

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

Herbert,
Yes, our assumptions are somewhat different. Each cell in column A may have
a different number of entries. Whereas you assume each cell has the same
number of entires (i.e., 4). Otherwise, we are in sync with everything else
you mentioned.
Is there a way to modify your formula to reflect my assumption?
Thanks again for all your help.
Bob

"Herbert Seidenberg" wrote:

Here is my setup. Maybe we assumed different initial conditions.
A1 thru A5 has this arbitrary text data:
A531, A493, C941, D526
G988, G400, H552, B584
F542, C723, H958, G598
K384, H410, C993, H223
E378, A721, C642, E549
After Text to Columns, I get at A1 thru D5:
A531 A493 C941 D526
G988 G400 H552 B584
F542 C723 H958 G598
K384 H410 C993 H223
E378 A721 C642 E549
I named A1:D5 arrayB. Verify that the Name Box shows this.
The formula entered at A15 and dragged down to A34 gave this:
A531
A493
C941
D526
G988
G400
H552
B584
F542
C723
H958
G598
K384
H410
C993
H223
E378
A721
C642
E549




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

When you define ArrayB, include as many columns as the biggest entry.
You will get lots of zeros in the output column.
To get rid of them and justify up, select the output and
Edit Go To Special Formulas Numbers
Delete Shift cells up
This assumes your data is text, as it is now.
If it is not, I got a fix for that too.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

On Mon, 19 Jun 2006 05:04:02 -0700, Bob wrote:

Herbert,
Yes, our assumptions are somewhat different. Each cell in column A may have
a different number of entries. Whereas you assume each cell has the same
number of entires (i.e., 4). Otherwise, we are in sync with everything else
you mentioned.
Is there a way to modify your formula to reflect my assumption?
Thanks again for all your help.
Bob


Bob,

You realize that the solution of this problem would be trivial and quick using
a VBA macro.

For example:

=================================
Option Explicit

Sub SplitData()
Dim src As Range
Dim dest As Range
Dim i As Long, j As Long
Dim SplitArray As Variant

Set dest = [B1]
i = 0: j = 0

For Each src In Selection
SplitArray = Split(src, ",")
For i = 0 To UBound(SplitArray)
dest.Offset(i + j, 0).Value = Trim(SplitArray(i))
Next i
j = j + UBound(SplitArray) + 1
Next src
End Sub
============================

allows you to select the range of cells you wish to split up, and generates a
single column list of all the contents of all the cells in "Selection".

This can be modified so you could only select one cell in the column; or
hard-code it; or ...

Then, instead of multiple steps, you just execute this macro and you're done.


--ron
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

Yes, I included as many columns as the biggest entry when I defined ArrayB.
In fact, the range ended up being N3:DD115. I then put your formula starting
in cell DE3. BTW, for whatever its worth, the original data range is F3:F115.
Your formula worked beautifully for the entries (19 of them) contained in
the first cell (F3). It broke out all 19 entries into the range DE3:DE21.
After that, I got all zeros in the range DE22:D278.
Maybe I'm still doing something wrong. Thanks again for your help.
Regards, Bob

"Herbert Seidenberg" wrote:

When you define ArrayB, include as many columns as the biggest entry.
You will get lots of zeros in the output column.
To get rid of them and justify up, select the output and
Edit Go To Special Formulas Numbers
Delete Shift cells up
This assumes your data is text, as it is now.
If it is not, I got a fix for that too.


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

Ron,
Your macro is perfect! Thanks a million!!!
I want the output to start in cell N3, so I changed your line "Set dest =
[B1]" to "Set dest = [N3]".
Rather than selecting the range I want to split up, could you tell me how I
can modify your macro to always have it start with cell F3?
Thanks again,
Regards, Bob

"Ron Rosenfeld" wrote:

On Mon, 19 Jun 2006 05:04:02 -0700, Bob wrote:

Herbert,
Yes, our assumptions are somewhat different. Each cell in column A may have
a different number of entries. Whereas you assume each cell has the same
number of entires (i.e., 4). Otherwise, we are in sync with everything else
you mentioned.
Is there a way to modify your formula to reflect my assumption?
Thanks again for all your help.
Bob


Bob,

You realize that the solution of this problem would be trivial and quick using
a VBA macro.

For example:

=================================
Option Explicit

Sub SplitData()
Dim src As Range
Dim dest As Range
Dim i As Long, j As Long
Dim SplitArray As Variant

Set dest = [B1]
i = 0: j = 0

For Each src In Selection
SplitArray = Split(src, ",")
For i = 0 To UBound(SplitArray)
dest.Offset(i + j, 0).Value = Trim(SplitArray(i))
Next i
j = j + UBound(SplitArray) + 1
Next src
End Sub
============================

allows you to select the range of cells you wish to split up, and generates a
single column list of all the contents of all the cells in "Selection".

This can be modified so you could only select one cell in the column; or
hard-code it; or ...

Then, instead of multiple steps, you just execute this macro and you're done.


--ron

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie


N3:DD115 has 95 columns. Does your biggest cell have 95 entries?
Assuming it has and the output range DE22:DE78 contains zeros,
(I assume DE22:D278 is a typo) then the output is correct.
You have to copy down 95 times till the next entry shows.



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

On Mon, 19 Jun 2006 10:33:01 -0700, Bob wrote:

Ron,
Your macro is perfect! Thanks a million!!!
I want the output to start in cell N3, so I changed your line "Set dest =
[B1]" to "Set dest = [N3]".
Rather than selecting the range I want to split up, could you tell me how I
can modify your macro to always have it start with cell F3?
Thanks again,
Regards, Bob


Bob,

How is the range defined?
What does the data look like?

Does the relevant range always end with the first blank cell at the bottom of
the column?

I've got to go to a meeting, but I'll get back on this when I return.

Some combination of the CurrentRegion property and Resize property will
probably do it.



--ron
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

Ron,

The range always starts with cell F3 and ends when it encounters the word
"TOTAL" in column F. Please note that within that range, there may be one or
more blank cells, so your macro would have to test for, and then skip over,
those cells until it encounters the "TOTAL" cell. Is that doable?

Thanks again for all your help.

Regards, Bob


"Ron Rosenfeld" wrote:

On Mon, 19 Jun 2006 10:33:01 -0700, Bob wrote:

Ron,
Your macro is perfect! Thanks a million!!!
I want the output to start in cell N3, so I changed your line "Set dest =
[B1]" to "Set dest = [N3]".
Rather than selecting the range I want to split up, could you tell me how I
can modify your macro to always have it start with cell F3?
Thanks again,
Regards, Bob


Bob,

How is the range defined?
What does the data look like?

Does the relevant range always end with the first blank cell at the bottom of
the column?

I've got to go to a meeting, but I'll get back on this when I return.

Some combination of the CurrentRegion property and Resize property will
probably do it.



--ron

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

On Mon, 19 Jun 2006 11:45:02 -0700, Bob wrote:

Ron,

The range always starts with cell F3 and ends when it encounters the word
"TOTAL" in column F. Please note that within that range, there may be one or
more blank cells, so your macro would have to test for, and then skip over,
those cells until it encounters the "TOTAL" cell. Is that doable?

Thanks again for all your help.

Regards, Bob


With that setup, it's easier than what I had in mind.

Try this:

================================
Sub SplitData()
Dim src As Range
Dim dest As Range
Dim i As Long, j As Long, k As Long
Dim SplitArray As Variant

Set src = [F3]
Set dest = [N3]
i = 0: j = 0
k = src.Row

Do Until Cells(k, src.Column).Text = "TOTAL"
SplitArray = Split(Cells(k, src.Column), ",")
For i = 0 To UBound(SplitArray)
dest.Offset(i + j, 0).Value = Trim(SplitArray(i))
Next i
j = j + UBound(SplitArray) + 1
k = k + 1
Loop
End Sub
===================================
--ron
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

Ron,
Your macro works perfectly! Thanks a million (and thanks for all your time
helping me)! I sincerely appreciate it.
Regards, Bob


"Ron Rosenfeld" wrote:

On Mon, 19 Jun 2006 11:45:02 -0700, Bob wrote:

Ron,

The range always starts with cell F3 and ends when it encounters the word
"TOTAL" in column F. Please note that within that range, there may be one or
more blank cells, so your macro would have to test for, and then skip over,
those cells until it encounters the "TOTAL" cell. Is that doable?

Thanks again for all your help.

Regards, Bob


With that setup, it's easier than what I had in mind.

Try this:

================================
Sub SplitData()
Dim src As Range
Dim dest As Range
Dim i As Long, j As Long, k As Long
Dim SplitArray As Variant

Set src = [F3]
Set dest = [N3]
i = 0: j = 0
k = src.Row

Do Until Cells(k, src.Column).Text = "TOTAL"
SplitArray = Split(Cells(k, src.Column), ",")
For i = 0 To UBound(SplitArray)
dest.Offset(i + j, 0).Value = Trim(SplitArray(i))
Next i
j = j + UBound(SplitArray) + 1
k = k + 1
Loop
End Sub
===================================
--ron

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

On Tue, 20 Jun 2006 05:46:01 -0700, Bob wrote:

Ron,
Your macro works perfectly! Thanks a million (and thanks for all your time
helping me)! I sincerely appreciate it.
Regards, Bob


Glad to help. Thanks for the feedback.

Be aware that the macro does no error checking and will not stop unless it
encounters the word TOTAL.

As written, TOTAL has to be the only content of that cell. If there might be
other stuff in the cell, you could use INSTR to look for TOTAL.

You could do some "sanity" checking by looking for TOTAL first, probably
looking from the bottom of src column up; and then aborting with an error
message if TOTAL is not found. If TOTAL is found, you could then set the range
to search to the appropriate range.


--ron


  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob
 
Posts: n/a
Default Extracting multiple entries in a cell into their component pie

Ron,
Thanks for the heads-up. Fortunately, TOTAL will always be the only content
of that cell.
With respect to performing a sanity check, I totally agree that it is wise
to do. But being a novice programmer, I'll have to wait until I get more
experience in coding (which I will by studying this discussion forum!).
Thanks again for all your help and time.
Regards, Bob


"Ron Rosenfeld" wrote:

On Tue, 20 Jun 2006 05:46:01 -0700, Bob wrote:

Ron,
Your macro works perfectly! Thanks a million (and thanks for all your time
helping me)! I sincerely appreciate it.
Regards, Bob


Glad to help. Thanks for the feedback.

Be aware that the macro does no error checking and will not stop unless it
encounters the word TOTAL.

As written, TOTAL has to be the only content of that cell. If there might be
other stuff in the cell, you could use INSTR to look for TOTAL.

You could do some "sanity" checking by looking for TOTAL first, probably
looking from the bottom of src column up; and then aborting with an error
message if TOTAL is not found. If TOTAL is found, you could then set the range
to search to the appropriate range.


--ron

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
Multiple Formulas in same cell C Anderson Excel Worksheet Functions 3 April 12th 06 03:31 AM
Multiple Formlas in same cell C Anderson Excel Discussion (Misc queries) 4 April 12th 06 03:19 AM
Counting Multiple Values In A Cell DiamondDean Excel Worksheet Functions 1 August 20th 05 07:22 AM
Identify repeated cell entries in multiple sheet workbook as you . Trigger Excel Discussion (Misc queries) 0 August 17th 05 01:57 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 06:22 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"