 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2006Extracting 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 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 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 > Put the pieces into separate cells with > Data > Text to Columns > Delimited > Comma, Space [quoted text clipped - 3 lines] > MOD(ROWS($1:1)-1,COLUMNS(arrayB))+1) > and copy down until you get #REF 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 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 > Here is my setup. Maybe we assumed different initial conditions. > A1 thru A5 has this arbitrary text data: [quoted text clipped - 31 lines] > C642 > E549 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. 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 > When you define ArrayB, include as many columns as the biggest entry. > You will get lots of zeros in the output column. [quoted text clipped - 3 lines] > This assumes your data is text, as it is now. > If it is not, I got a fix for that too. 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. >Herbert, >Yes, our assumptions are somewhat different. Each cell in column A may have [quoted text clipped - 4 lines] >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 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 > >Herbert, > >Yes, our assumptions are somewhat different. Each cell in column A may have [quoted text clipped - 43 lines] > > --ron >Ron, >Your macro is perfect! Thanks a million!!! [quoted text clipped - 4 lines] >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 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, > >Your macro is perfect! Thanks a million!!! [quoted text clipped - 19 lines] > > --ron >Ron, > [quoted text clipped - 6 lines] > >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 Ron, Your macro works perfectly! Thanks a million (and thanks for all your time helping me)! I sincerely appreciate it. Regards, Bob > >Ron, > > [quoted text clipped - 34 lines] > =================================== > --ron >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 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, > >Your macro works perfectly! Thanks a million (and thanks for all your time [quoted text clipped - 15 lines] > > --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.