SOLVED

Formatting help with Transpose

%3CLINGO-SUB%20id%3D%22lingo-sub-1772398%22%20slang%3D%22en-US%22%3EFormatting%20help%20with%20Transpose%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1772398%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20split%20the%20Standard%20released%20column%20in%20such%20a%20way%20after%20the%20semicolon%20it%20should%20in%20the%20next%20row.%20Please%20see%20attached%20excel%20sheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInput%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sbhan4_0-1602526438909.png%22%20style%3D%22width%3A%20836px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226071i95C1E2AA5E01C25F%2Fimage-dimensions%2F836x117%3Fv%3D1.0%22%20width%3D%22836%22%20height%3D%22117%22%20role%3D%22button%22%20title%3D%22sbhan4_0-1602526438909.png%22%20alt%3D%22sbhan4_0-1602526438909.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOutput%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sbhan4_1-1602526796646.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226073i5344A06D600CB216%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22sbhan4_1-1602526796646.png%22%20alt%3D%22sbhan4_1-1602526796646.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-1772398%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1772728%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20help%20with%20Transpose%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1772728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F808107%22%20target%3D%22_blank%22%3E%40sbhan4%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eeasiest%20way%20is%20with%20Power%20Query%3C%2FP%3E%0A%3CP%3E-%20convert%20the%20source%20to%20table%20or%20name%20it%20as%20Range%20and%20query%3C%2FP%3E%0A%3CP%3E-%20split%20first%20column%20texts%20to%20list%20with%20semicolon%20delimiters%3C%2FP%3E%0A%3CP%3E-%20expand%20lists%20to%20rows%3C%2FP%3E%0A%3CP%3E-%20load%20result%20to%20Excel%20sheet%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20638px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226105i709F342C9EB688B1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi everyone,

I am trying to split the Standard released column in such a way after the semicolon it should in the next row. Please see attached excel sheet

 

Input 

sbhan4_0-1602526438909.png

 

Output

sbhan4_1-1602526796646.png

 

4 Replies
Highlighted
Best Response confirmed by sbhan4 (Occasional Contributor)
Solution

@sbhan4 

easiest way is with Power Query

- convert the source to table or name it as Range and query

- split first column texts to list with semicolon delimiters

- expand lists to rows

- load result to Excel sheet

image.png

Highlighted

@Sergei Baklan 

 

Thanks for your help!

 

I am using a power query.

 

-splitting columns by delimiter - done

- How to expand lists to rows?

Highlighted

@Sergei Baklan It worked perfectly!! thanks !!!

Highlighted

@Sergei Baklan It worked ..thanks