如何在Excel中将格式不一致的数据转换为结构化格式

针对这种带分类标签的不规则换行数据，常规公式搞不定的话，优先用Power Query或自定义VBA，这俩对格式不一致的数据兼容性更强：

方法1：Power Query（推荐，无代码） #

1. 选中数据列，点击「数据」选项卡 → 「从表格/区域」（勾选「我的表格有标题」）
2. 在Power Query编辑器里，选中目标列，点击「添加列」→ 「自定义列」，输入以下M语言代码（替换原始列为你实际的列名）：

   Location = if List.Count(List.Select(Splitter.SplitTextByDelimiter("#(lf)")([原始列]), each Text.StartsWith(_, "Location:")))>0 then Text.AfterDelimiter(List.Select(Splitter.SplitTextByDelimiter("#(lf)")([原始列]), each Text.StartsWith(_, "Location:")){0}, ": ") else null,
   Host = if List.Count(List.Select(Splitter.SplitTextByDelimiter("#(lf)")([原始列]), each Text.StartsWith(_, "Host:")))>0 then Text.AfterDelimiter(List.Select(Splitter.SplitTextByDelimiter("#(lf)")([原始列]), each Text.StartsWith(_, "Host:")){0}, ": ") else null,
   Guest = if List.Count(List.Select(Splitter.SplitTextByDelimiter("#(lf)")([原始列]), each Text.StartsWith(_, "Guest:")))>0 then Text.AfterDelimiter(List.Select(Splitter.SplitTextByDelimiter("#(lf)")([原始列]), each Text.StartsWith(_, "Guest:")){0}, ": ") else null,
   Bucket = if List.Count(List.Select(Splitter.SplitTextByDelimiter("#(lf)")([原始列]), each Text.StartsWith(_, "Bucket:")))>0 then Text.AfterDelimiter(List.Select(Splitter.SplitTextByDelimiter("#(lf)")([原始列]), each Text.StartsWith(_, "Bucket:")){0}, ": ") else null
   

3. 点击「关闭并上载」，数据会自动按标签拆分到对应列，无对应标签的单元格会填充null，不会生成冗余列。

方法2：自定义VBA函数 #

按Alt+F11打开VBA编辑器，插入模块，粘贴以下代码：

Function GetTagValue(cell As Range, tag As String) As String
    Dim arr() As String
    arr = Split(cell.Value, vbLf)
    Dim i As Integer
    For i = LBound(arr) To UBound(arr)
        If InStr(arr(i), tag & ":") > 0 Then
            GetTagValue = Trim(Mid(arr(i), InStr(arr(i), ":") + 1))
            Exit Function
        End If
    Next i
    GetTagValue = ""
End Function

回到Excel，在空白单元格输入=GetTagValue(A1, "Location")，下拉填充即可提取对应标签的值；替换第二个参数为Host/Guest/Bucket，就能提取其他标签的内容。

方法3：动态数组公式进阶（适用于Excel 365） #

如果不想用工具或代码，试试这个组合公式：

=LET(
    arr, TEXTSPLIT(A1, CHAR(10)),
    filterTag, FILTER(arr, ISNUMBER(SEARCH("Location:", arr))),
    IFERROR(TRIM(TEXTAFTER(filterTag, ": ")), "")
)

把A1换成目标单元格，复制公式到其他列时，替换"Location:"为对应标签即可，无匹配内容时会显示空白。

内容的提问来源于stack exchange，提问作者Saad