SOLVED

Semicolon cel to rows conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-2377805%22%20slang%3D%22en-US%22%3ESemicolon%20cel%20to%20rows%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2377805%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20an%20easy%20way%20in%20excel%20to%20convert%20this%20(old)%20situation%20to%20the%20(new)%20one%3F%20Some%20great%20function%20combinations%20would-be%20very%20welcome.%3C%2FP%3E%3CP%3Ethanks%20in%20advance%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Pieter2000_0-1621688316701.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282836iF36DC8E594CC0585%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Pieter2000_0-1621688316701.png%22%20alt%3D%22Pieter2000_0-1621688316701.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2377805%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2377850%22%20slang%3D%22en-US%22%3ERe%3A%20Semicolon%20cel%20to%20rows%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2377850%22%20slang%3D%22en-US%22%3EThis%20is%20pretty%20straightforward%20using%20Power%20Query%3A%3CBR%20%2F%3E%3CBR%20%2F%3EClick%20inside%20one%20of%20the%20cells%20in%20your%20%22(old%22)%20range.%20Go%20to%20Data%2C%20From%20Table%2FRange%2C%20confirm%20the%20range%2C%20and%20check%20the%20%22My%20table%20has%20headers%22%20checkbox.%20Ok.%20The%20Power%20Query%20editor%20will%20open.%20Click%20on%20the%20header%20for%20the%20second%20column%20%22Y(old)%22.%20Go%20to%20the%20menu%20item%2C%20Home%20%E2%86%92%20Split%20Column%20%E2%86%92%20by%20Delimeter.%20Ensure%20the%20delimter%20is%20%22Semicolon%22%2C%20%22Each%20occurrence%20of%20the%20delimiter%22%2C%20expand%20the%20%22Advanced%20Options%22%2C%20and%20choose%20to%20split%20into%20Rows.%20Click%20Ok.%20Then%20go%20to%20Home%20%E2%86%92%20Close%20%26amp%3B%20Load.%20A%20new%20worksheet%20will%20be%20created%20with%20your%20reformatted%20data.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2378800%22%20slang%3D%22en-US%22%3ERe%3A%20Semicolon%20cel%20to%20rows%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2378800%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F907536%22%20target%3D%22_blank%22%3E%40BlairHall%3C%2FA%3E%26nbsp%3Bthank%20you%20very%20much.%20That%20works%20great.%20(Should%20have%20discovered%20power%20query%20earlier)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Is there an easy way in excel to convert this (old) situation to the (new) one? Some great function combinations would-be very welcome.

thanks in advance

Pieter2000_0-1621688316701.png

 

2 Replies
best response confirmed by Pieter2000 (New Contributor)
Solution

This is pretty straightforward using Power Query:

Click inside one of the cells in your "(old)" range. Go to Data, From Table/Range, confirm the range, and check the "My table has headers" checkbox. Ok. The Power Query editor will open. Click on the header for the second column "Y(old)". Go to the menu item, Home → Split Column → by Delimeter. Ensure the delimter is "Semicolon", "Each occurrence of the delimiter", expand the "Advanced Options", and choose to split into Rows. Click Ok. Then go to Home → Close & Load. A new worksheet will be created with your reformatted data.

@BlairHall thank you very much. That works great. (Should have discovered power query earlier)