Split values in cell into columns and rows

user_13562186 0
Viewed 578

Is it possible to split the data within one cell into both cells and rows? I understand with power query you can split into 2 columns using , as delimiter but I wonder If it would also be possible to split this data into rows and columns using a formula. The desired output in reality can be anywhere in the spreadsheet (in reality will be in sheet2).

enter image description here

office365 powerquery delimiter excel-formula excel
3 Answers

In Power Query you can split into both rows and columns as per your question.

  • Remove the commas
  • Split into rows using <line feed> as the delimiter
  • Split into columns using the <space> as the delimiter

-M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),

    //Remove the extraneous commas
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",",","",Replacer.ReplaceText,{"Column2"}),

    //Split by linefeed into rows
    #"Split Column by Delimiter" = Table.ExpandListColumn(
        Table.TransformColumns(#"Replaced Value", {
            {"Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let
                 itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2", type text}}),
    
    //split on the <space> into columns
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column2", 
        Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column2.1", "Column2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column2.1", type text}, {"Column2.2", type text}})
in
    #"Changed Type2"

enter image description here

user_28729220

Through formulae as per question:

enter image description here

Formula in A6:

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,REPT(A2:A3&"</s><s>",LEN(B2:B3)-LEN(SUBSTITUTE(B2:B3,CHAR(10),""))+1))&"</s></t>","//s[.!='']")

Formula in B6:

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(CHAR(10),,B2:B4),",","")&CHAR(10),"ppm"&CHAR(10)," ")," ","</s><s>")&"</s></t>","//s[.*0!=0][.!='']")

Formula in C6:

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(CHAR(10),,B2:B4),",","")&CHAR(10),"ppm"&CHAR(10)," ")," ","</s><s>")&"</s></t>","//s[.*0=0]")

Though I'd also recommend PowerQuery as per the other answer.

user_97581940

Yes. Through Microsoft Excel Text To Columns function found in Menu Data > Text To Columns.

The ideal text will usually be Mono-spaced(requires monospace font) or delimited by a comma or some other type of delimiter.

Feed good data = better results

Be sure to select the Range of cells you are going to "split apart" BEFORE CLICKING ON Text To Columns.

image1

user_6871370
Related