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

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

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

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

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

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.

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