It will ask you for a target cell, which is a cell where the selection will be copied (or rather where the copying will start) with the required splitting. To use it, just select the area that you want to convert and run the macro. At this point, PowerQuery shows the table with the values split into separate rows. You can leave the quote character as a double quote ('). Specify that the delimiter is a comma, that you want to split on each occurrence, and, under the advanced options, tick the radio box for Rows. MsgBox ("You didn't enter valid cell address") Then, click Split Column and select By Delimiter. In this case, it will be '80.' Click the B2 cell so it's selected. To split the obtained marks, click the D2 cell and manually type the marks for the B2 cell. RowCumulationTotal = rowCumulationTotal + rowCumulationCurrent In the 'Data' tab, from the 'Data Tools' section, select 'Flash Fill.' And instantly, Excel will split the names from all the B column records and add those to the C column. RowCumulationCurrent = rowCumulationCurrent + 1 Select the cells you want to split Select the ribbons Data tab Click the Text to Columns button in the Data Tools group In step 1 of the wizard, make sure. Set pasteCell = Range(InputBox("Please enter target cell address"))ĭim rowCumulationCurrent As Integer, ma圎lemsOnRow As Integerĭim elems() As String, elemCount As IntegerĮlems = Split(Cells(Selection.Row + i - 1, Selection.Column + j - 1), vbLf)Ĭells(pasteCell.Row + i + rowCumulationTotal + k - 1, pasteCell.Column + j - 1) = elems(k) If you are willing to use VBA, here is a macro that will do it: Sub splitByNewLine()ĭim pasteCell As Range, rowCumulationTotal As Integer
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |