Text to columns using a formula
 

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 Bloch

Text to columns using a formula
 

Can you post some representative samples of your data?

Biff

wrote in message
oups.com...
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 Bloch

Text to columns using a formula
 

Hi Joe,
Excel Developer Tip: The versatile Split function
http://www.j-walk.com/ss/excel/tips/tip93.htm


http://groups.google.com/groups?thre...%40tkmsftngp09


---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

wrote in message oups.com...
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 Bloch

Text to columns using a formula
 

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 "Bill" in B1, "Joe" in C1, "Bob" in
D1, "Fred" in B2, and so forth.

Thanks for the help.

Joe

Text to columns using a formula
 

Have 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




" wrote:

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 Bloch

Text to columns using a formula
 

That "Extract an Element" thing was precisely what I needed. Thanks,
David McRitchie!

Joe

Text to columns using a formula
 

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

Biff

wrote in message
oups.com...
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 "Bill" in B1, "Joe" in C1, "Bob" in
D1, "Fred" in B2, and so forth.

Thanks for the help.

Joe