Performing a text to rows operation

{ 20 Comments }

By Ashish Mathur · · , · Leave a comment

Given multiple text values in a cell which are separated by a semi-colon, one may want to split data into multiple rows along with existing data in other columns.  This is something similar to Data > Text to columns – the only difference being that data has to be split by rows instead of columns.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

You may watch a short video of my solution here

Solution for all versions of MS Excel

I have also shown a macro based solution to this problem.

You may refer to my solution in this workbook.

For accomplishing just the reverse i.e. merging data from multiple cells into a single cell (separated by a comma), you may refer to my solution at this link.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Hi,
    There is a problem inviting your solution.
    In the file (Link: http://sdrv.ms/Pn8eez ) I want you to generate a two column database as shown in range A2081:B2129 of sheet named “Data”. I also want to generate the count in column C i.e. C2081:C2129.

    Thanks in advance
    Haque

    Reply

  • Hi,
    I opened the file, pressed ALT+F8, but no macro name was found.
    Perhaps, it was accidentally deleted. Please check.

    Regards
    Haque

    Reply

    • Hi,

      That is strange. Anyways, in the file which I posted, paste the following VBA code in the Code window

      Sub split_cell_into_rows()
      Application.ScreenUpdating = False
      Dim varItm As Variant
      Dim rngText As Range
      Dim rows_source As Long
      Dim rngCl As Range
      Dim arrtext() As String
      Dim rows_in_output As Long
      Dim rows_for_formula As Long
      Set rngText = Worksheets(“Data”).Range(Range(“E2”), Range(“E2”).End(xlDown))
      Worksheets(“Data”).Range(“A2069”).Value = “Subject codes”
      Worksheets(“Data”).Range(“B2069”).Value = “Subject”
      Worksheets(“Data”).Range(“C2069”).Value = “Count”
      Worksheets(“Data”).Range(“A2069:C2069”).Font.Bold = True
      Worksheets(“Data”).Range(“A2069:C2069″).Borders(xlEdgeBottom).Weight = xlThin
      i = 2070
      For Each rngCl In rngText
      arrtext = Split(Application.WorksheetFunction.Trim(rngCl), ” “)
      For Z = 1 To Application.WorksheetFunction.CountA(arrtext)
      Worksheets(“Data”).Cells(i, 1) = arrtext(Z – 1)
      Worksheets(“Data”).Cells(i, 2) = rngCl.Offset(0, 1).Value
      i = i + 1
      Next Z
      Next rngCl
      rows_in_output = Worksheets(“Data”).Range(“A” & Rows.Count).End(xlUp).Row
      ActiveSheet.Range(“A2069:B” & rows_in_output).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
      rows_source = Worksheets(“Data”).Range(“E2”).End(xlDown).Row
      Worksheets(“Data”).Range(“C2070”).Formula = “=SUMPRODUCT(1*(ISNUMBER(SEARCH(A2070,$E$2:$E$” & rows_source & “))))”
      rows_in_formula = Worksheets(“Data”).Range(“A” & Rows.Count).End(xlUp).Row
      Worksheets(“Data”).Range(“C2070”).AutoFill Destination:=Range(“C2070:C” & rows_in_formula)
      Application.Goto Reference:=Worksheets(“Data”).Range(“A2069”), scroll:=True
      Application.ScreenUpdating = True
      End Sub

      Hope this helps.

      Reply

  • How to count Certain Code Number Located in a column having multiple codes in each cell
    I have a workbook in which there are two worksheets: “Data” and “Code Counts”
    In Data sheet, the G Column Heading is “SUBJECTS” codes taken by students for study. In G Column each cell contains multiple subject codes separated by commas eg. 101,102,107,108,127,128,174,175,176,177,178,179 The cells are formatted as Text.

    My Question is
    How can I know what are the unique codes located in the SUBJECTS column?
    The File is here: http://sdrv.ms/YzBfdQ

    Reply

    • Hi,

      You may refer to three solutions (1 macro and 2 non macro solutions) in the workbook which you may download from here

      The second non macro solution uses Power Query – a free MS Excel add-in from Microsoft for Excel 2010 + versions.

      Reply

  • i ran your macro program as below:

    Sub split_cell_into_rows()
    Dim varItm As Variant
    Dim rngText As Range
    Dim rngCl As Range
    Dim arrtext() As String
    Dim i As Integer
    Set rngText = Worksheets(“test1”).Range(“B3:B” & Worksheets(“test1”).Range(“B” & Rows.Count).End(xlUp).Row)
    i = 3
    For Each rngCl In rngText
    arrtext = Split(rngCl, “|”)
    For Z = 1 To Application.WorksheetFunction.CountA(arrtext) – 2
    Worksheets(“test2”).Cells(i, 2) = arrtext(Z)
    Worksheets(“test2”).Cells(i, 1) = rngCl.Offset(0, -1).Value
    i = i + 1
    Next Z
    Next rngCl
    Worksheets(“test2”).Activate
    Range(“A2”).Select
    End Sub

    ==============================
    but i get an error (run time error ’13’ Type mismatch)

    Reply

    • Hi,

      There are three ways of going about solving your problem

      1. Non macro based solution – using MS Word

      a. Copy data from range A2:G4 of sheet1 and paste in MS Word
      b. In MS Word, press Ctrl+H. Find for | and Replace with ^p. Click on Replace All
      c. Copy the resultant table from MS Word and paste in back in a new worksheet (say you name the tab as Non Macro solution) of your MS Excel workbook
      d. Select range A2:F21 of Non Macro solution worksheet and click on Merge and Centre icon
      e. Select A2:F21 and press Ctrl+G > Special > Blanks > OK
      f. Press = and then up arrow
      g. Press Ctrl+Enter

      2. Non macro based solution – using MS Power Query – a free MS Excel add-in from Microsoft for Excel 2010 + versions.

      3. Macro based solution

      You may refer to my solutions in this workbook.

      Hope this helps.

      Reply

  • Can you please explain how to use the Power Query part of your solution? I do not see how from the add-in ribbon options.

    Thank you!

    Reply

  • Hi Ashish,

    Thank you SO MUCH for your extremely helpful video and worksheet macro.
    I’m using Excel for Mac 2016, so cannot get/run Power Query!
    You saved me hours of work!

    Question: Can your solution can be adapted for a more complex problem?
    I also have data like this:

    |Col-1 | Col-2 | Col-3 | Col-4 | Col-5| Col-6 | Col-7 |Column8 |
    |AAA1 | BBB1 | CCC1 | DDD1 | EEE1 | FFF1 | ABC123 |;text1;text2;text3;|
    |AAA2 | BBB2 | CCC2 | DDD2 | EEE2 | FFF2 | EFG456 |;text4;text5;text6;text7|

    that I need to convert to rows (where data in Columns 1-7 are repeated for each ;field; in Column 8), i.e.

    |AAA1 | BBB1 | CCC1 | DDD1 | EEE1 | FFF1 | ABC123 |text1|
    |AAA1 | BBB1 | CCC1 | DDD1 | EEE1 | FFF1 | ABC123 |text2|
    |AAA1 | BBB1 | CCC1 | DDD1 | EEE1 | FFF1 | ABC123 |text3|
    |AAA2 | BBB2 | CCC2 | DDD2 | EEE2 | FFF2 | EFG456 |text4|
    |AAA2 | BBB2 | CCC2 | DDD2 | EEE2 | FFF2 | EFG456 |text5|
    |AAA2 | BBB2 | CCC2 | DDD2 | EEE2 | FFF2 | EFG456 |text6|
    |AAA2 | BBB2 | CCC2 | DDD2 | EEE2 | FFF2 | EFG456 |text7|

    I hope you can help or have some advice. Thanks so much,

    Peter W

    Reply

    • Hi,

      Without Power Query the process is lengthy but try this. I hope this works on a Mac as well:

      1. Suppose your data is in range A2:H3. Headings are in A1:H1
      2. Copy A2:H3 and paste it as a Table in a Word document
      3. In the Word document, press Ctrl+H. In the Find What box, type ; and in the replace with box, type ^p. Click on Replace All
      4. Copy the resultant table from MS Word and paste it back in the Excel file
      5. Select data in the first seven columns and click on the Merge icon to unmerge data in those columns
      6. Select the data (including blanks) in the first seven columns and press Ctrl+G > Special > Blanks
      7. Press = and then the up arrow key on the keyboard. Press Ctrl+Enter
      8. Select the data in the first seven columns, copy it > right click > Paste Special > Values > OK

      Hope this helps.

      Reply