It can be easily adapted to process any columns range and return even overwriting the existing range, but this should be done only after checking that it return what you need. It processes the range in columns "B:D" and returns the result in columns "G:I". K = 1 'initialize the variable to load the final arrayĪrrSp = Split(Replace(arr(i, 1)," ",""), " ") 'trim for the case when somebody used Red Blue, instead of Red BlueĪrrFin(k, 1) = arrSp(j): arrFin(k, 3) = arr(i, 3): k = k + 1ĪrrSp = Split(Replace(arr(i, 1)," ",""), " ")ĪrrFin(k, 2) = arrSp(j): arrFin(k, 3) = arr(i, 3): k = k + 1 ReDim arrFin(1 To UBound(arr) * 10, 1 To 3) 'maximum to keep max 10 rows per each case Or just use this, change Set SplitRange Range ('A1:A1000') as appropriate to the range you want to split the strings from. I tried it with multiple cells selected when I wrote it. You should just be able to select the whole range you want to split and then run it. LastR = sh.Range("B" & sh.unt).End(xlUp).row Re: Formula to split cell values to multiple cells based on condition. Select Comma as the delimiter, and uncheck any other delimiters. Click the Separator button that appears, and. Click on the Text to Columns icon in the Data Tools group of the Excel ribbon and a wizard will appear to help you set up how the text will be split. Select and right-click cells containing text (B1:B3) and in the Menu, go to Data > Split text to columns. It uses arrays and should be very fast for big ranges to be processed, working mostly in memory: Sub testSplitInsert()ĭim sh As Worksheet, lastR As Long, arr, arrSp, arrFin, i As Long, j As Long, k As Long Click on the Data tab in the Excel ribbon. Offset(, thirdColValuesOffsetFromRefCel).Value = thirdColValue With refCel.Offset(, mainValuesOffsetFromRefCel) MainValuesOffsetFromRefCel As Long, thirdColValuesOffsetFromRefCel As Long) Cells(.Rows.Count, "B").End(xlUp).Offset(1), _ĬurrSecondColValues, thirdColValues(iRow, 1), _ĬurrMainColValues As Variant, thirdColValue As Variant, _ Cells(.Rows.Count, "C").End(xlUp).Offset(1), _ĬurrFirstColValues, thirdColValues(iRow, 1), _
Grouped data in the same Microsoft Excel cell. Both ways, Text to Columns and Flash Fill are easy to do in just a few steps.
EXCEL SPLIT CELLS MULTIPLE COLUMNS CODE
Offset(, 1).Value = thirdColValues(iRow, 1)įollow the code step by step by pressing F8 while the cursor is in any code line in the VBA IDE and watch what happens in the Excel user interfaceĪdding edited code for a more "parametric" handling by means of a helper function Sub SplitB圜ol() There are two ways to split Microsoft Excel data into multiple columns. Offset(, 2).Value = thirdColValues(iRow, 1) Cells(.Rows.Count, "B").End(xlUp))įor iRow = LBound(firstColValues) To UBound(firstColValues)ĬurrFirstColValues = Split(firstColValues(iRow, 1), " ")ĬurrSecondColValues = Split(secondColValues(iRow, 1), " ") Click the "Data" tab and then click "Text to Columns.With. It'll need to contain two pieces of data separated by a "delimiter." This'll usually be a comma, period, dash, or space.Ģ. In Excel, select the cell, group of cells, or entire column that has the text you want to split.
EXCEL SPLIT CELLS MULTIPLE COLUMNS HOW TO
How to split cells into columns using a delimiterġ. For example, if you have a list of names, you can tell Excel to split the data so one column has the first letter of everyone's name, and the second column has the rest of the name. The Fixed Width option lets you manually pick where you want Excel to split the data.For example, if you've written out someone's name as "Smith, John," it'll separate Smith and John into separate columns. In Excel, you can use the Text to Columns functionality to split the content of a cell into multiple cells.You can specify the delimiter (such as a space, co.