☰

Excel 公式从单元格中提取键和值

tag-icon • tag-icon microsoft-excel worksheet-function google-spreadsheets
Excel 公式从单元格中提取键和值

在 Excel 中，我有重复的单元格，其中包含以下一组键/值（这是带有换行符的单个单元格）：

utmSource:Gmail; 
utmMedium:email; 
utmCampaign:Live; 
utmTerm:software; 
deviceType:desktop;

我想提取每个键的值，例如单元格中的 utmSource 是什么、utmMedium 是什么等等。公式如何实现这一点？

答案1

“单元格的 utmSource 是什么，utmMedium 是什么，等等。”

这听起来像是某种查找，因此我会回答这个问题，就好像你想在你的单元格中执行查找一样（也就是说，不是提取整个值数组本身）。

通过 Excel：

在此处输入图片描述

公式B3：

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A$1,":",";"),CHAR(10),";"),";","</s><s>")&"</s></t>","//s[preceding::*[1]='"&A3&"']")

通过 Google 表格：

在此处输入图片描述

公式B3：

=REGEXEXTRACT(A$1,A3&":([^;]+)")

要检索所有信息（键和值），您可以使用A3：

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(SUBSTITUTE(A1,";",""),CHAR(10))),":"))

但一切都取决于您想如何使用这些信息。

答案2

您也可以使用宏。下面的代码假设您的键/值集位于 A 列，从单元格 A1 开始。它会遍历 A1 列中的单元格，然后在 B 列中输入键，在 C 列中输入值。

Sub SplitKeyPairs()
    'Variables
    Dim myString As String
    Dim kpArr() As String
    Dim i, j As Integer

    i = 1
    j = 1
    
    Do
        myString = Range("A" & i)
    

        'Split the string based on the line break
        For Each keypair In Split(myString, vbLf)
            'Split the keypair value
            kpArr = Split(keypair, ":")
        
            'Copy keys on B column and values on C column
            Range("B" & j) = kpArr(0)
            Range("C" & j) = Replace(kpArr(1), ";", "")

            j = j + 1

        Next

        i = i + 1
    Loop While Range("A" & i) <> ""
End Sub

键/值对将如下图所示出现。

图片:

答案3

我想建议两种可能的方法。一种是，Text to Column另一种是Excel formula。

在此处输入图片描述

怎么运行的：

方法 1：

  • 选择源数据。

  • 从德按下 TAB 键电视延伸至列。

  • 选择选项德消除。

  • 然后在下一个屏幕上选择哦来自分隔符列表并:在框中进行。

  • 然后选择德o 不通过选择第一列来导入列（跳过）。

  • 选择单元格Q24为德目的地。

在此处输入图片描述

  • 现在打F完成。

注意：

  • 即使文本位于单元格中且由换行符分隔，显示的方法也将有效。

  • 按Ctrl+J框哦thers 作为分隔符。

在此处输入图片描述

方法 2：

  • 单元格中的公式R24：

    =MID(N24,FIND(":",N24)+1,LEN(N24))

注意： 您可以根据需要调整公式中的单元格引用。

相关内容