Forum Home
Press F1
Thread ID: 123148	2012-02-07 00:21:00	Excel split cell containing multiple values	Mike (15)	Press F1

Post ID	Timestamp	Content	User
1257836	2012-02-07 00:21:00	Hi all, I have a spreadsheet here at work that lists asset numbers in one cell, then the tasks that are required in other cells in the same row. I need to split these into a single asset record per row. Similar to how the text-to-columns works, but I need it to rows, and the remaining cell values in each row to be copied to each record. So I've got this: Record# Location Task 123, 234, 345 Home Replace 321, 432 Work Repair 111, 222, 333 Somewhere else Ignore and I need to have this: Record# Location Task 123 Home Replace 234 Home Replace 345 Home Replace 321 Work Repair 432 Work Repair 111 Somewhere else Ignore 222 Somewhere else Ignore 333 Somewhere else Ignore Any ideas on how I could go about this? Some rows have 10+ values to split, some have only 1 or 2 (well wouldn't need to split the rows with only 1 record). I'm wanting to do this so I can do a VLOOKUP from another spreadsheet... if its possible to get the VLOOKUP to see a single value within a cell of multiple values, then this would be preferable I guess? Cheers, Mike.	Mike (15)
1257837	2012-02-07 02:02:00	I don't know anything about VLOOKUP but you could use the left, right, mid commands to seperate out the data. I used Excel help to come up with this and I don't completely get it but for a test with 3 digits numbers seperated by commas it seems to work, you may have to tweak it some. With the folloing in cell A1; 123, 456, 789 =LEFT(A1,SEARCH(",",A1,1)-1) Gives 123 =MID(A1,SEARCH(",",A1,1)+1,SEARCH(",",A1,SEARCH(",",A1,1))) Gives 456 =RIGHT(A1,SEARCH(",",A1,1)-1) Gives 789 hope that's helpful	dugimodo (138)
1257838	2012-02-07 03:29:00	You can't arrange/import/paste to have only one record per cell? Otherwise you might in for some complex/macro forumlation. You could try by starting from text to columns to separate the data. Otherwise VLoockup or Pivot tables may help (since can group-ungroup-reorder-orientate different ways), if you have several of the same numbered groups of records, but then they ideally need have unique records per cell to start with.	kahawai chaser (3545)
1257839	2012-02-07 20:00:00	I don't know anything about VLOOKUP but you could use the left, right, mid commands to seperate out the data. I used Excel help to come up with this and I don't completely get it but for a test with 3 digits numbers seperated by commas it seems to work, you may have to tweak it some.Thanks for the suggestion dugimodo, however the search/left/mid/right functions won't work well in this case as there aren't always the same number of values in each cell. I might be able to combine this with some VBA to automatically go through and grab the left record then copy down each time, but am hoping to find an easier way. You can't arrange/import/paste to have only one record per cell? Otherwise you might in for some complex/macro forumlation. You could try by starting from text to columns to separate the data. Otherwise VLoockup or Pivot tables may help (since can group-ungroup-reorder-orientate different ways), if you have several of the same numbered groups of records, but then they ideally need have unique records per cell to start with.Hi kahawai chaser, no I can't copy/paste down so there's only one record per cell. This has been provided to me in this format, and there are too many records to reorder it manually. The examples I put in my first post were very simplified - there are a lot more rows, a lot more columns, and a greater range of values (and number of values) in each cell. I guess I'm going to have to figure out a macro to split the values out - was just hoping I wouldn't have to. Cheers, Mike.	Mike (15)
1257840	2012-02-07 20:47:00	Company I worked for used Microsoft Access to do similar. I think a form was created with fields and updated related records. Then all records were split out and filtered in rows and columns to reorder/merge relationships amongst the various fields and records. Maybe search online for VB scripts (www.google.co.nz excel+cells&psj=1&oq=forum:vb+script+to+split+data+to+rearrange+from +excel+cells&aq=f&aqi=&aql=&gs_sm=e&gs_upl=2919l2919l0l4638l1l1l0l0l0l0l597l597l5-1l1l0&bav=on.2,or.r_gc.r_pw.,cf.osb&fp=61b4c99010eafeec&biw=1024&bih=472) for cell splitting or teach excel (www.teachexcel.com) might have what you need.	kahawai chaser (3545)
1257841	2012-02-07 22:18:00	Thanks for your suggestions - I've written a macro to do it for me. Probably not the tidiest but it works :) Sub RenewalYearSplitValues() Application.ScreenUpdating = False Dim allVal Dim leftVal Dim remainVal Dim newRec Dim recCount Dim currentCell Dim currentRow Dim currentCol ' Select cell in first row of records (row 2) Range("F2").Select Dim usedRows usedRows = ActiveCell.Row Do While usedRows > 0 ' Keep track of cells/rows being assessed currentCell = ActiveCell.Address currentRow = ActiveCell.Row currentCol = ActiveCell.Column ' Get the value from column A ' (possibly contains multiple records in the cell) allVal = Trim(Range("A" & currentRow).Value) ' Sometimes there's an extra comma, so if there is remove it If Not IsNumeric(Right(Trim(allVal), 1)) Then allVal = Left(allVal, Len(allVal) - 1) End If Dim s() As String Dim i As Integer ' Split the multi-record cell whenever comma found s = Split(allVal, ",") For i = 0 To UBound(s) s(i) = Trim(s(i)) Next ' This is how many records from cell (remember starts at 0) recCount = UBound(s) ' Multi records in cell have count greater than 0 ' If multi records, then create new rows for each additional record If (recCount > 0) Then Rows((currentRow + 1) & ":" & currentRow + (recCount)).Select Selection.Insert shift:=xlDown End If ' Go back to the row being looked at Range(currentCell).Select ' For every record from multi-record cell, input in column A ' New row for each record ' Copy other cells down for additional columns For i = 0 To UBound(s) Range("a" & currentRow + i).Select ActiveCell.FormulaR1C1 = s(i) If (i > 0) Then Range("b" & currentRow + i & ":e" & currentRow + i).Select Selection.FillDown End If Next ' Move down to next record Range("F" & currentRow + i).Select ' If no new record, then Exit the loop (finished) ' otherwise loop through new row If (Range("a" & ActiveCell.Row).Value = "") Then Exit Do Else usedRows = ActiveCell.Row End If Loop Application.ScreenUpdating = True End Sub Cheers, Mike.	Mike (15)
1