software

In Excel 2003... I've got a column with several items (between 0 and 7
items) delineated by commas. I need to be able to break out the various
items into adjacent cells (so that, for example, if the original data
is in column A, the first item would be in column B, the second (if
present) would be in column C, etc.

The kicker is that I can NOT do this using the Tools: Text to Columns
wizard. This is something that needs to recalculate in real-time, as
the items in column A change (they feed in from another sheet in the
workbook).

I've made some progress using a combination of MID and FIND (keying in
on the commas), with different formulae in columns B-H (one for the
first item, one for the second, one for the third, etc.) but I've only
gotten the first one to work properly.

Anyone ever done anything like this before?

Joe BlochCan you post some representative samples of your data?

Biff

gt; wrote in message oups.com...
gt; In Excel 2003... I've got a column with several items (between 0 and 7
gt; items) delineated by commas. I need to be able to break out the various
gt; items into adjacent cells (so that, for example, if the original data
gt; is in column A, the first item would be in column B, the second (if
gt; present) would be in column C, etc.
gt;
gt; The kicker is that I can NOT do this using the Tools: Text to Columns
gt; wizard. This is something that needs to recalculate in real-time, as
gt; the items in column A change (they feed in from another sheet in the
gt; workbook).
gt;
gt; I've made some progress using a combination of MID and FIND (keying in
gt; on the commas), with different formulae in columns B-H (one for the
gt; first item, one for the second, one for the third, etc.) but I've only
gt; gotten the first one to work properly.
gt;
gt; Anyone ever done anything like this before?
gt;
gt; Joe Bloch
gt;
Hi Joe,
Excel Developer Tip: The versatile Split function
www.j-walk.com/ss/excel/tips/tip93.htmgroups.google.com/groups?thre...@tkmsftngp09---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

gt; wrote in message oups.com...
gt; In Excel 2003... I've got a column with several items (between 0 and 7
gt; items) delineated by commas. I need to be able to break out the various
gt; items into adjacent cells (so that, for example, if the original data
gt; is in column A, the first item would be in column B, the second (if
gt; present) would be in column C, etc.
gt;
gt; The kicker is that I can NOT do this using the Tools: Text to Columns
gt; wizard. This is something that needs to recalculate in real-time, as
gt; the items in column A change (they feed in from another sheet in the
gt; workbook).
gt;
gt; I've made some progress using a combination of MID and FIND (keying in
gt; on the commas), with different formulae in columns B-H (one for the
gt; first item, one for the second, one for the third, etc.) but I've only
gt; gotten the first one to work properly.
gt;
gt; Anyone ever done anything like this before?
gt;
gt; Joe Bloch
gt;
This isn't the exact data, but it'll give you an idea of what I'm
working with:

A1: Bill, Joe, Bob
A2: Fred, Sam
A3:
A4: Thomas, Beauregard, Nancy, Jane, Sally, Horace, Ed
A5:
A6: Heather
A7: Tom, Carl

etc...

So there can be up to seven comma-delimited items in each cell, or
zero. What I need to end up with is quot;Billquot; in B1, quot;Joequot; in C1, quot;Bobquot; in
D1, quot;Fredquot; in B2, and so forth.

Thanks for the help.

JoeHave you considered using a User Defined Function written in VBA. Are you
looking for help with that? If so, what version of Excel will this be used
in. Are you familiar with VBA?

--
Regards,
Tom Ogilvy

quot; wrote:

gt; In Excel 2003... I've got a column with several items (between 0 and 7
gt; items) delineated by commas. I need to be able to break out the various
gt; items into adjacent cells (so that, for example, if the original data
gt; is in column A, the first item would be in column B, the second (if
gt; present) would be in column C, etc.
gt;
gt; The kicker is that I can NOT do this using the Tools: Text to Columns
gt; wizard. This is something that needs to recalculate in real-time, as
gt; the items in column A change (they feed in from another sheet in the
gt; workbook).
gt;
gt; I've made some progress using a combination of MID and FIND (keying in
gt; on the commas), with different formulae in columns B-H (one for the
gt; first item, one for the second, one for the third, etc.) but I've only
gt; gotten the first one to work properly.
gt;
gt; Anyone ever done anything like this before?
gt;
gt; Joe Bloch
gt;
gt;

That quot;Extract an Elementquot; thing was precisely what I needed. Thanks,
David McRitchie!

JoeOk, well I see you like David's suggestion (as do I), so.....

Biff

gt; wrote in message oups.com...
gt; This isn't the exact data, but it'll give you an idea of what I'm
gt; working with:
gt;
gt; A1: Bill, Joe, Bob
gt; A2: Fred, Sam
gt; A3:
gt; A4: Thomas, Beauregard, Nancy, Jane, Sally, Horace, Ed
gt; A5:
gt; A6: Heather
gt; A7: Tom, Carl
gt;
gt; etc...
gt;
gt; So there can be up to seven comma-delimited items in each cell, or
gt; zero. What I need to end up with is quot;Billquot; in B1, quot;Joequot; in C1, quot;Bobquot; in
gt; D1, quot;Fredquot; in B2, and so forth.
gt;
gt; Thanks for the help.
gt;
gt; Joe
gt;