Split text on power query based on left column

[Bibhuti Mohanty]

Dear All Excel Ninja,

The requirement of mine to split text to column multiple delimiter based on the left column field to 5 Column on power query.
Please check the bellow data. I need the output on C1,C2,C3,C4,C5 Column from Column 2.

Thanks for consideration of this mail.

Column 1	Column 2	C 1	C 2	C 3	C 4	C 5
05B8507	82110K55R0020200000/3798/83402K76K20/R000	82110K55R00	20200000	3798	83402K76K20	R000
05B8512	82110K55R0020200000/3798/83402K76K20/R000	82110K55R00	20200000	3798	83402K76K20	R000
05B8515	82110K55R00WV0001070339 HuKan Rec OL PVT/R000	82110K55R00	WV0001070339		HuKan Rec OL PVT	R000
05B8515	82110K55R00WV0001070439 HuKan Rec OL PVT/R000	82110K55R00	WV0001070439		HuKan Rec OL PVT	R000
05D0502	25970K79K10234687959/208630311396/1318111/19082020	25970K79K10	234687959	208630311396	1318111	19082020
05D0502	82110K54T00234673882/208630311338/1316029/18082020	82110K54T00	234673882	208630311338	1316029	18082020
05D0502	82110K55R00750931672/208630311252/1608982/17082020	82110K55R00	750931672	208630311252	1608982	17082020
05D0503	83401K76K2083401K76K20/229/0/5503001372/SUPPK000371	83401K76K20	83401K76K20	229	5503001372	SUPPK000371
05D0503	83402K76K2083402K76K20/229/0/5503001364/SUPPK000371	83402K76K20	83402K76K20	229	5503001364	SUPPK000371
05D0503	83402K76K2083402K76K20/229/0/5503001375/SUPPK000371	83402K76K20	83402K76K20	229	5503001375	SUPPK000371
05D7399	83401K76K20234721652/208630311682/1316029/20082020	83401K76K20	234721652	208630311682	1316029	20082020
05D7399	83501K55R00234710664/208630311632/1316029/20082020	83501K55R00	234710664	208630311632	1316029	20082020
05D7399	83502K79K10234742404/208630311781/1316029/21082020	83502K79K10	234742404	208630311781	1316029	21082020
3190605	82110K54T00234641118/208630311197/1316029/17082020	82110K54T00	234641118	208630311197	1316029	17082020
3190605	25970K79K10234641320/208630311210/1318111/17082020	25970K79K10	234641320	208630311210	1318111	17082020
3190605	DEKO LIV FULL SCE OBG	DEKO LIV FULL SCE OBG

Regards,

Bibhuti

 

[Excel Wizard]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplacedValue = Table.ReplaceValue(Source,"/0/","/",Replacer.ReplaceText,{"Column 2"}),
    DuplicatedColumn = Table.DuplicateColumn(ReplacedValue, "Column 2", "Column"),
    SplitRight = Table.SplitColumn(DuplicatedColumn, "Column", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Column", "C 5"}),
    SplitSlash = Table.SplitColumn(SplitRight, "Column", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column", "C 3", "C4"}),
    SplitSpace = Table.SplitColumn(SplitSlash, "Column", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column", "Co4"}),
    Split11 = Table.SplitColumn(SplitSpace, "Column", Splitter.SplitTextByPositions({0, 11}, false), {"C1", "C 2"}),
    AddC1 = Table.AddColumn(Split11, "C 1", each if [C 5] = null then [Column 2] else [C1]),
    AddC4 = Table.AddColumn(AddC1, "C 4", each if [C 5] = null then null else if [C4] = null then [Co4] else [C4]),
    Sort = Table.SelectColumns(AddC4,{"C 1", "C 2", "C 3", "C 4", "C 5"})
in
    Sort