将单元格中的值拆分为列和行 [英] Split values in cell into columns and rows

问题描述

是否可以将一个单元格中的数据拆分为单元格和行?我了解使用power查询可以将用作分隔符分成两列，但我想知道是否还可以使用公式将这些数据分为行和列.实际上，所需的输出可以在电子表格中的任何位置(实际上将在sheet2中).

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).

推荐答案

在Power Query中，您可以根据自己的问题分为 行和列.

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

• 删除逗号
• 使用<换行符> 作为分隔符将其拆分为行
• 使用< space> 作为分隔符将其拆分为列

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

- M代码

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"

这篇关于将单元格中的值拆分为列和行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！