Share via

Convert semicolon separated cell into rows

Anonymous
2017-02-10T19:01:45+00:00

Hi,

I have following data in Excel. I want to convert semicolon separated cell into rows.

Id              Name          Status              Contacts

1234    Test Tesing     ACTV           Apple Rose; Test Testing; Mtest Testy; Aaaaa Test; Deavc Test; Stest Ctest

1232    Test Easy       ACTV            Aasd Ntestl; Mtestt Btesttler; Htest Mtest; Testravis Kesting; Etest Nest

Results

Id      Name         Status    Contacts

1234 Test Tesing ACTV Apple Rose

1234 Test Tesing ACTV Test Testing

1234 Test Tesing ACTV Mtest Testy

1234 Test Tesing ACTV Aaaaa Test

1234 Test Tesing ACTV Deavc Test

1234 Test Tesing ACTV Stest Ctest

1232 Test Easy ACTV Aasd Ntestl

1232 Test Easy ACTV Mtestt Btesttler

1232 Test Easy ACTV Htest Mtest

1232 Test Easy ACTV Testravis Kesting

1232 Test Easy ACTV Etest Nest

Thanks,

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2017-02-15T00:25:51+00:00

    Hi,

    1. Create the spare column as shown in the screenshot of the previous post.  The formula in cell D2 is

    =COUNTIF(A$2:A2,A2)

    1. Select the entire dataset and go to Data > Get & Transform > From Table > OK
    2. Click on any cell in the Dummy column and under Transform, click on Pivot Column
    3. In the Value section, select Crs_Name
    4. Under Aggregation, select No aggregation
    5. Click on OK
    6. Click on Close and Load.

    Hope this helps.

    2 people found this answer helpful.
    0 comments

14 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2017-02-11T00:24:45+00:00

    Hi,

    You can use Get & Transform in Excel 2016.  Try this

    1. Select the entire dataset and to go Data > Get & Transform > From Table
    2. Right click on the heading of the last column and go to Split column > Other > Semicolon > OK
    3. Right click on the first three columns and select Unpivot other columns
    4. Click on Close and Load

    Hope this helps.

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2017-02-13T14:16:13+00:00

    Thanks HansV, Ashish. Very helpful. Saved lot of time.

    0 comments
  3. Anonymous
    2017-02-11T03:30:41+00:00

    Thanks Ashish.

    Never 'saw' this option...

    0 comments
  4. HansV 462.6K Reputation points MVP Volunteer Moderator
    2017-02-10T20:14:09+00:00

    Here is a macro:

    Sub Transform()

        Dim r As Long

        Dim m As Long

        Dim arr() As String

        Dim i As Long

        Application.ScreenUpdating = False

        m = Range("A" & Rows.Count).End(xlUp).Row

        For r = m To 2 Step -1

            arr = Split(Range("D" & r).Value, ";")

            For i = UBound(arr) To 1 Step -1

                Range("A" & r).EntireRow.Copy

                Range("A" & r + 1).EntireRow.Insert

                Range("D" & r + 1).Value = Trim(arr(i))

            Next i

            Range("D" & r) = Trim(arr(0))

        Next r

        Application.CutCopyMode = False

        Application.ScreenUpdating = True

    End Sub

    0 comments