Save the spreadsheet containing the original worksheet and the transformed worksheet. The great advantage of Power Query is that Excel will remember all the steps you took to transform the data. A new worksheet will appear to the left of the original worksheet with the product data split into rows in a table. Click OK and the data preview will now show each order occupying a number of lines - one for each product ordered. Click on Advanced options and in the Split Into option, choose Rows. If the products are separated by a comma and then a space, select Custom and enter a comma followed by a space. Select Split Column/By Delimiter and select the delimiter that is separating the product names in the cell. Highlight the column containing the products ordered. In the Get & Transform section, select From Table to open the Power Query editor. Select any cell in your data and press Ctrl+T to convert it into a table.
#EXCEL SPLIT CELLS COMMA DOWNLOAD#
If you don’t see these tools on the Data tab, you can download Power Query for Excel 2010 or Excel 2013 from Microsoft. If you’re running Office 365 or Excel 2016, you’ll find the Power Query tools in Data/Get & Transform.
#EXCEL SPLIT CELLS COMMA FREE#
In our article on the new features in Excel ( (read more about this), see The next step ), we mentioned a new integral feature called Power Query (it was previously a free add-in for certain versions of Excel 20).
#EXCEL SPLIT CELLS COMMA UPDATE#
An easier way is to take advantage of a 2017 update to Excel’s Power Query function which lets you split the data in a cell by a delimiter and have the results appear as new rows. But using Text to Columns in this scenario would result in lots of extra columns - one for each product which would then need to be transposed somehow into rows. In (read more about this) (see The next step ), we explained how you could use Excel’s Text to Columns function to split cell data separated by a comma (or other delimiter such as a semi-colon or space). To make it easier to determine which products have been ordered, you would like to split the data in that cell so that each product ordered gets its own row. Each order takes up one row in the spreadsheet but the problem is that each product ordered is contained in a single cell, separated by a comma.
So how can you do this?Įach day the sales department sends you a spreadsheet of sales orders that have been received. While Text to Columns would break the data out of the one cell, it wouldn’t create new rows. You want to split comma separated values in one cell into several rows, stacked in a single column. The built-in text-to-columns only outputs to the same row, so a custom split like you have created is the way to go.Disciplinary, dismissal and grievance matters I don't think this will work for your case, because you're taking values from one row and putting them in another row.
The other method as Pieter suggests below would be to record (and modify) a macro using the built-in Text-to-Columns functionality. So you can simply do: Range("a10").Resize(1, UBound(X) + 1).Value = XĪlternatively, omit the first argument but include a comma to indicate that you're only passing the second argument: Range("a10").Resize(, UBound(X) + 1).Value = X For example, Debug.Print Range("A10").Resize(10).Address should give you: $A$10:$A$19, when what you want is $A$10:$J$10.ĭo this instead: Range("a10").Resize(1, UBound(X) - LBound(X) + 1).Value = XĪlso you're removing the call to Application.Transpose, since the array is already in a row, you don't need to transpose it (previously you had to transpose it from a row, to a column).ĪLSO the result of the Split function is always a 0-based array, even if you have Option Base 1. You have only supplied the first argument, which resizes A10 to a specified number of rows.
The Resize method takes two arguments, both optional. You're resizing Range("A10") in to a column range.
0 kommentar(er)
