Text to columns using a formula

[jbloch]

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

 

[Biff]

Can you post some representative samples of your data?

Biff

 

[David McRitchie]

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


http://groups.google.com/groups?threadm=OvRoxkigCHA.1544@tkmsftngp09

 

[jbloch]

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

 

[Tom Ogilvy]

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?

 

[jbloch]

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

Joe

 

[Biff]

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

Biff