Multiple Split of single cell

[Basavaraj K H]

Dear Team


i have attached a file , where i have given an example of my problem ,

i want to split a single cell into multiple cells (may be even 10 cells ) based on a criteria example

input = 10-5X7X12,12X1X20,6X13X11( in single cell)

Output =
10
5
7
12
12
1
20
6
13
11
( all in different cell horizontally)

Thanks in Advance for giving solution or trying to solve

 

[bosco_yip]

Maybe..................



1] In F4, copied down :

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($4:$7)/((LEN($D$4:$D$7)-LEN(SUBSTITUTE($D$4:$D$7,",",""))+1)>=COLUMN($A:$G)),ROWS($1:1))),"")

2] In G4, copied down :

=IF($F4="","",VLOOKUP(F4,$B$4:$D$7,2,0))

3] In H4, copied down :

=IF($F4="","",LEFT(VLOOKUP(F4,$B$4:$D$7,3,0),FIND("-",VLOOKUP(F4,$B$4:$D$7,3,0))-1))

4] In I4, copied across and down :

=IF($F4="","",TRIM(MID(SUBSTITUTE("X"&TRIM(MID(SUBSTITUTE(","&SUBSTITUTE(VLOOKUP($F4,$B$4:$D$7,3,0),$H4&"-",""),",",REPT(" ",50)),COUNTIF($F$4:$F4,$F4)*50,50)),"X",REPT(" ",50)),COLUMN(A1)*50,50)))

Regards
Bosco

 

[rahulshewale1]

hii @Basavaraj K H ,


PFA power query solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sl No", Int64.Type}, {"Docket No", type text}, {"Text Details", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","X","-",Replacer.ReplaceText,{"Text Details"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value", "Text Details", "Text Details - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Text Details - Copy", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Text Details - Copy.1", "Text Details - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text Details - Copy.1", Int64.Type}, {"Text Details - Copy.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Text Details - Copy.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Text Details - Copy.2.1", "Text Details - Copy.2.2", "Text Details - Copy.2.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Text Details - Copy.2.1", type text}, {"Text Details - Copy.2.2", type text}, {"Text Details - Copy.2.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Text Details"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Sl No", "Docket No", "Text Details - Copy.1"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns1", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Value.1", Int64.Type}, {"Value.2", Int64.Type}, {"Value.3", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Text Details - Copy.1", "1"}, {"Value.1", "2"}, {"Value.2", "3"}, {"Value.3", "4"}})
in
    #"Renamed Columns"