I want to separate name and id from ColA to ColB as name and ColC as ID's please share formula. Name / Primary - ID / Secondary - ID...
Separation Formula Required
-
-
-
Using Power Query, see the attached file. Here is the Mcode
Code
Display Morelet Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name / Primary - ID / Secondary - ID", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Name / Primary - ID / Secondary - ID", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Name / Primary - ID / Secondary - ID.1", "Name / Primary - ID / Secondary - ID.2", "Name / Primary - ID / Secondary - ID.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name / Primary - ID / Secondary - ID.1", type text}, {"Name / Primary - ID / Secondary - ID.2", type text}, {"Name / Primary - ID / Secondary - ID.3", Int64.Type}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Name / Primary - ID / Secondary - ID.2", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Name / Primary - ID / Secondary - ID.2.1", "Name / Primary - ID / Secondary - ID.2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Name / Primary - ID / Secondary - ID.2.1", Int64.Type}, {"Name / Primary - ID / Secondary - ID.2.2", type text}}), #"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"Name / Primary - ID / Secondary - ID.1", "Name / Primary - ID / Secondary - ID.2.2"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Name / Primary - ID / Secondary - ID.2.1", type text}, {"Name / Primary - ID / Secondary - ID.3", type text}}, "en-US"),{"Name / Primary - ID / Secondary - ID.2.1", "Name / Primary - ID / Secondary - ID.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged.1") in #"Merged Columns1"
-
Adapt the formulas that I gave you in your previous post.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!