Split column into multiple rows

[lafm5970]

Hello,

I am using Excel for O365 and I have a spreadsheet with multiple rows/columns like this:

orig dest
IA 37029, 37030, 37031, 37032, 37033
IL 37029, 37030, 37031, 37032, 37034
IN 37029, 37030, 37031, 37032, 37035

I like to split the 'dest' column into multiple rows keeping 'orig' as is, by selectin the number of desired characters.

Desire results when setting 'dest' size to 10 characters:

orig dest
IA 37029, 370
IA 30, 37031,
IA 37032, 37
IA 033
IL 37029, 370
IL 30, 37031,
IL 37032, 37
IL 034
IN 37029, 370
IN 30, 37031,
IL 37032, 37
IN 37032, 37
IN 035

Thank you!

[alansidman]

This is a quick Unpivot using Power Query or in 365 as Get and Transform.


Here is the Mcode. If you are not familiar with PQ, then click on the links in my signature.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

EDIT: Just relooked at your request. It appears that all values are in two columns. Is that correct. Second column is separated by commas. I misinterpreted the data to be in several columns. Please confirm your layout of data and I will look at a new solution.

[alansidman]

I think I figured out what you want. Try this Mcode instead

let
    Source = Excel.CurrentWorkbook(){[Name="Table1_2"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Source, "Column1.2", Splitter.SplitTextByRepeatedLengths(10), {"Column1.2.1", "Column1.2.2", "Column1.2.3", "Column1.2.4"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Position", {"Column1.1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

My results look like the following:

v	A	B
1	Column1.1	Value
2	IA	37029, 370
3	IA	30, 37031,
4	IA	37032, 37
5	IA	033
6	IL	37029, 370
7	IL	30, 37031,
8	IL	37032, 37
9	IL	034
10	IN	37029, 370
11	IN	30, 37031,
12	IN	37032, 37
13	IN	035

[lafm5970]

This worked perfectly!!! Thank you very much.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.