Bill Jelen mentioned this in his year-end tips for the MyExcelOnline podcast, and if you’d like to check it out, it is here. For instance, if there were three lines of data in a single cell in the row, then the data in that cell should be split out into three rows.First, let’s split the column at each semicolon into rows. (The data in the cell was separated into lines by pressing Alt+Enter between items.) James would like to split this data into multiple rows. One of the columns in the data includes cells that have multiple lines per cell.The problem is that while this successfully splits the data into separate columns, it doesn't get it into separate rows, like James requested.Got too much information in a single cell Here's how you can use a macro to pull apart that information and put it into cells in the way you want. This can be used to split the data based on the presence of the ASCII 10 character, which is what Excel inserts when you press Alt+Enter. Excel provides a handy way to split data into separate columns using the Text to Columns tool. The Split Column by Delimiter dialog opens, and we select the.In addition, the split-apart versions of the cells are stored in a new worksheet, so that the original worksheet is not affected at all.The macro relies upon the use of the Split function to tear apart the multi-line cells. As written here, the macro splits apart info in the fourth column. Range("A65536").End(xlUp).RowNote that in order to run the macro, you will need to specify, using the iColumn variable, the column that contains the cells to be split apart. Range("IV1").End(xlToLeft).ColumnLNumRows =. In this example, the macro assumes that you want to "expand" everything in the worksheet, and that the data in the worksheet starts in row 1.LNumCols =. One approach is shown in the following code.The address of the upper-left cell in the new worksheet is the same as the upper-left cell selected when the macro is run. The macro only works on the cells selected when it is first run, and the split-apart cells are transferred to a new worksheet. In this instance, the variable is iSplitCol, and it is set to column 4. To overcome all these potential problems, you will want to consider the following macro, instead:For lRow = (lRowOffset + 1) To (lRowOffset + lRows)For i = (iColOffset + 1) To (iColOffset + iCols)The macro still relies upon the use of a variable to indicate the column to be split apart. In addition, you might want to only run the macro on a particular selection of cells.
0 Comments
Leave a Reply. |
Details
AuthorErin ArchivesCategories |