Search code examples
excelexcel-formulaexcel-2010powerquery

Separating a cell with values and moving related columns to a new row

I'm trying to separate a column with multiple values - separated by commas - and move the each value to the next row, while inheriting related values from different columns.

I found solutions related to R, Perl etc. However, I want to handle this with excel or excel-vba. I had also tried using 'Text to Column' feature of excel, then mapping the new cell with a direct link, but this doesn't solve my problem.

Below is the excel sheet:

Name         Subject       Topic    Tags
Chinedu      Mathematics   Algebra  Algebra, Trigonometry,  Maths
Sophia       English       Comp     English,comprehension, New tag
Sonia        English       Essay    English, Essay Writing,Tag
John         Maths.        Trig.    Maths, Trigonometry, New

The expected Output is:

Name         Subject       Topic    Tags
Chinedu      Mathematics   Algebra  Algebra
Chinedu      Mathematics   Algebra  Trigonometry
Chinedu      Mathematics   Algebra  Maths
Sophia       English       Comp     English
Sophia       English       Comp     comprehension
Sophia       English       Comp     New tag
John         Maths.        Trig.    Maths
John         Maths.        Trig.    Trigonometry
John         Maths.        Trig.    New
Solution

  • You can use #powerquery which is available in Excel 2010 and later versions.

    Please refer to this article to find out how to use Power Query on your version of Excel.

    The logic is to load the source data to the Power Query Editor, use Split Column function under the Transform tab to split the Tags column by comma , as the delimiter, and put the results into Rows.

    Split Columns

    Then you should have the following:

    Output

    You can Trim and Clean the Tags column to make the data neat and tidy then load the output table to a new worksheet (by default) or create a connect of the query and choose to load it to a specific location in your workbook.

    Here are the power query M code behind the scene for reference only. You only need to use the built-in functions of the editor to get the desired result.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Subject", type text}, {"Topic", type text}, {"Tags", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Tags", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Tags"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Tags", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Tags", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Tags", Text.Clean, type text}})
in
    #"Cleaned Text"

    Let me know if you have any questions. Cheers :)