Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pieces
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pieces
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
Yes, I included as many columns as the biggest entry when I defined ArrayB.
In fact, the range ended up being N3:DD115. I then put your formula starting in cell DE3. BTW, for whatever its worth, the original data range is F3:F115. Your formula worked beautifully for the entries (19 of them) contained in the first cell (F3). It broke out all 19 entries into the range DE3:DE21. After that, I got all zeros in the range DE22:D278. Maybe I'm still doing something wrong. Thanks again for your help. Regards, Bob "Herbert Seidenberg" wrote: 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
Ron,
Your macro is perfect! Thanks a million!!! I want the output to start in cell N3, so I changed your line "Set dest = [B1]" to "Set dest = [N3]". Rather than selecting the range I want to split up, could you tell me how I can modify your macro to always have it start with cell F3? Thanks again, Regards, Bob "Ron Rosenfeld" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
N3:DD115 has 95 columns. Does your biggest cell have 95 entries? Assuming it has and the output range DE22:DE78 contains zeros, (I assume DE22:D278 is a typo) then the output is correct. You have to copy down 95 times till the next entry shows. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
On Mon, 19 Jun 2006 10:33:01 -0700, Bob wrote:
Ron, Your macro is perfect! Thanks a million!!! I want the output to start in cell N3, so I changed your line "Set dest = [B1]" to "Set dest = [N3]". Rather than selecting the range I want to split up, could you tell me how I can modify your macro to always have it start with cell F3? Thanks again, Regards, Bob Bob, How is the range defined? What does the data look like? Does the relevant range always end with the first blank cell at the bottom of the column? I've got to go to a meeting, but I'll get back on this when I return. Some combination of the CurrentRegion property and Resize property will probably do it. --ron |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
Ron,
The range always starts with cell F3 and ends when it encounters the word "TOTAL" in column F. Please note that within that range, there may be one or more blank cells, so your macro would have to test for, and then skip over, those cells until it encounters the "TOTAL" cell. Is that doable? Thanks again for all your help. Regards, Bob "Ron Rosenfeld" wrote: On Mon, 19 Jun 2006 10:33:01 -0700, Bob wrote: Ron, Your macro is perfect! Thanks a million!!! I want the output to start in cell N3, so I changed your line "Set dest = [B1]" to "Set dest = [N3]". Rather than selecting the range I want to split up, could you tell me how I can modify your macro to always have it start with cell F3? Thanks again, Regards, Bob Bob, How is the range defined? What does the data look like? Does the relevant range always end with the first blank cell at the bottom of the column? I've got to go to a meeting, but I'll get back on this when I return. Some combination of the CurrentRegion property and Resize property will probably do it. --ron |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
On Mon, 19 Jun 2006 11:45:02 -0700, Bob wrote:
Ron, The range always starts with cell F3 and ends when it encounters the word "TOTAL" in column F. Please note that within that range, there may be one or more blank cells, so your macro would have to test for, and then skip over, those cells until it encounters the "TOTAL" cell. Is that doable? Thanks again for all your help. Regards, Bob With that setup, it's easier than what I had in mind. Try this: ================================ Sub SplitData() Dim src As Range Dim dest As Range Dim i As Long, j As Long, k As Long Dim SplitArray As Variant Set src = [F3] Set dest = [N3] i = 0: j = 0 k = src.Row Do Until Cells(k, src.Column).Text = "TOTAL" SplitArray = Split(Cells(k, src.Column), ",") For i = 0 To UBound(SplitArray) dest.Offset(i + j, 0).Value = Trim(SplitArray(i)) Next i j = j + UBound(SplitArray) + 1 k = k + 1 Loop End Sub =================================== --ron |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
Ron,
Your macro works perfectly! Thanks a million (and thanks for all your time helping me)! I sincerely appreciate it. Regards, Bob "Ron Rosenfeld" wrote: On Mon, 19 Jun 2006 11:45:02 -0700, Bob wrote: Ron, The range always starts with cell F3 and ends when it encounters the word "TOTAL" in column F. Please note that within that range, there may be one or more blank cells, so your macro would have to test for, and then skip over, those cells until it encounters the "TOTAL" cell. Is that doable? Thanks again for all your help. Regards, Bob With that setup, it's easier than what I had in mind. Try this: ================================ Sub SplitData() Dim src As Range Dim dest As Range Dim i As Long, j As Long, k As Long Dim SplitArray As Variant Set src = [F3] Set dest = [N3] i = 0: j = 0 k = src.Row Do Until Cells(k, src.Column).Text = "TOTAL" SplitArray = Split(Cells(k, src.Column), ",") For i = 0 To UBound(SplitArray) dest.Offset(i + j, 0).Value = Trim(SplitArray(i)) Next i j = j + UBound(SplitArray) + 1 k = k + 1 Loop End Sub =================================== --ron |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
On Tue, 20 Jun 2006 05:46:01 -0700, Bob wrote:
Ron, Your macro works perfectly! Thanks a million (and thanks for all your time helping me)! I sincerely appreciate it. Regards, Bob Glad to help. Thanks for the feedback. Be aware that the macro does no error checking and will not stop unless it encounters the word TOTAL. As written, TOTAL has to be the only content of that cell. If there might be other stuff in the cell, you could use INSTR to look for TOTAL. You could do some "sanity" checking by looking for TOTAL first, probably looking from the bottom of src column up; and then aborting with an error message if TOTAL is not found. If TOTAL is found, you could then set the range to search to the appropriate range. --ron |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting multiple entries in a cell into their component pie
Ron,
Thanks for the heads-up. Fortunately, TOTAL will always be the only content of that cell. With respect to performing a sanity check, I totally agree that it is wise to do. But being a novice programmer, I'll have to wait until I get more experience in coding (which I will by studying this discussion forum!). Thanks again for all your help and time. Regards, Bob "Ron Rosenfeld" wrote: On Tue, 20 Jun 2006 05:46:01 -0700, Bob wrote: Ron, Your macro works perfectly! Thanks a million (and thanks for all your time helping me)! I sincerely appreciate it. Regards, Bob Glad to help. Thanks for the feedback. Be aware that the macro does no error checking and will not stop unless it encounters the word TOTAL. As written, TOTAL has to be the only content of that cell. If there might be other stuff in the cell, you could use INSTR to look for TOTAL. You could do some "sanity" checking by looking for TOTAL first, probably looking from the bottom of src column up; and then aborting with an error message if TOTAL is not found. If TOTAL is found, you could then set the range to search to the appropriate range. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Formulas in same cell | Excel Worksheet Functions | |||
Multiple Formlas in same cell | Excel Discussion (Misc queries) | |||
Counting Multiple Values In A Cell | Excel Worksheet Functions | |||
Identify repeated cell entries in multiple sheet workbook as you . | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |