Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to columns using a formula
That "Extract an Element" thing was precisely what I needed. Thanks,
David McRitchie! Joe |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text to formula - Help pleaaase | Excel Discussion (Misc queries) | |||
Justify text across multiple columns | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Text to Columns - moves text up | Excel Discussion (Misc queries) |