如何使用 Excel 解析 Google Analytics UTM URL

Question

基本方法是通过查找起点和终点来找到去除字符串片段的方法。

如果您的字符串有一个常规分隔符来划分感兴趣的信息，您可以使用一个巧妙的技巧，FILTERXML()使用它轻松地为您的输出单元格挑选出每一部分。然而，您的数据并非如此。

所以，有点难。不是很难理解，只是需要做很多事情，因为你需要从字符串中得到 11 个数据。

我已经改变了您给出的字符串，将您的“网站网址”替换为您想要的输出中显示的“ht tps://www.mysite.com”，并给出我在以下材料中使用的这个字符串：

ht tps://www.mysite.com/directory/another-directory?utm_id=inc0000000&utm_source=facebook&utm_medium=affiliate&utm_campaign=2021+q3+campaign&utm_content=post&utm_adsize=1200x628&utm_audience=general&utm_product=loan+product&promocode=250offer

（假设“ht tps”的两种用法实际上都是“https”......如果我正确使用它们，这个编辑器会显示错误。）

第一个部分相当简单。您知道起点。它是字符 1。因此，您可以使用更容易理解的LEFT()函数。更容易理解，因为编写查找停止点的部分并不太复杂。

（严格来说，它会找到要取出的字符串的长度，但这也是一个停止点，所以它符合我描述你的方法的语言。但重要的是要知道它实际上是你要计算的长度。）

那么，如何告诉 Excel“在此停止”？您需要在字符串中找到足够独特的东西，但仍然靠近（距离越远，就越难确定）您想要的停止点。幸运的是，“.com”后面有一个“/”可以使用。有一个函数FIND()可以定位它的位置。但有一个问题：在字符串结尾之前有两个。不想让 Excel 挑选其中一个！但FIND()也可以让您告诉 Excel 从哪里开始查找，这样您就可以告诉它“在 A1 中查找“/”，但从字符 10 开始查找”，这样它就永远不会注意到前两个。

那么，第一位的公式将是：

=LEFT(A1,  FIND("/",A1,  FIND("//",A1)+2) - 1)

Excel 找到“/”（位置 23）。您希望该字符之前的字符作为结尾，因此您从该字符中减去 1。因此 Excel 最终从字符 1 开始，并使用所有字符，直到 22。请注意，如果网站名称更长或更短，Excel 不会变慢，因为您没有直接使用它的名称，只是使用它周围的内容。

接下来的九个位遵循相同的思路：使用所需数据前后的字符串位来找到起点和终点。您将使用函数MID()来实现这些。它需要知道从哪里查找、从哪里开始以及从开始到结束要走多远。这些公式假设数据在单元格 A1 中，这样就完成了。找到可以在之前和之后使用的内容完全取决于字符串中的确切内容。请注意，这些公式适用于您在问题中给出的字符串，但如果每个字符串都有不同的分隔符，则它们将不适用于不同的字符串。

因此，对于其余的目录结构数据，使用：

=MID(A1,  FIND("/", A1, FIND("//", A1)+2),  FIND("?",A1) - FIND("/", A1, 
FIND("//", A1)+2) - 1)

A1 后面的部分是起始位置。它以与在那里找到它完全相同的方式找到网站 URL 的第一个单个“/”。它通过查找目录材料后面的“？”来找出在停止之前要读取多少个字符。但您不需要那个实际数字。您需要从中减去 1，因为它比正确的结尾多了一个字符，并且您需要减去起始点之前的所有字符。这就是最后FIND()一部分中的第二个给您的结果（就像它在第二部分中帮助您找到起始点一样），您可以看到之后减去了 1。

这九个位中的最后八位使用相同的想法，但它们有方便使用的字符串片段，如“utm_source=”，因为它们不重复，并且恰好出现在每个片段之前和之后。您可以使用较短的片段，但我更喜欢使用整个片段，因为这样更容易选择，其中有“奇数”的“utm_”和“=”，因此它们不太可能出现在您想要提取的材料中（这会破坏它们的用途），如果选择整个片段，那么选择它们的原因似乎更清楚。所以它们看起来像这样：

=MID(A1,  FIND("utm_id=",A1) + LEN("utm_id="),  FIND("utm_source=",A1) - (FIND("utm_id=",A1) + LEN("utm_id=")) - 1)

第一个找到“utm_id”并使用它四次。前两个找到它的起始点，然后将其长度添加到该起始点以找到所需字符串的起始点。然后找到以下“utm_”字符串的开头（并减去 1）以找到结束位置，然后最后两次使用“utm_id”减去起始长度。因此，您已隔离“inc0000000”位。

您可以打开公式编辑器行（单元格上方）并突出显示，然后复制公式，然后转到接下来的七个单元格，打开公式编辑器（“F2”）并粘贴它们。然后它们只需要编辑要搜索的精确字符串。（如果您在公式中使用 $A$1 而不是 A1 — 您确实应该这样做（我只是在回答问题，而不是构建电子表格）— 您可以复制整个单元格，就像您以前可能做过的那样，然后将其粘贴到七个单元格中。这有点容易……

无论如何，现在所有八个单元格中的公式都相同。浏览您粘贴的七个单元格，并替换公式中的“utm_something”字符串，直到您编辑了所有七个单元格，并且它们都有自己独特的配对。一定要从大字符串中直接复制准确的字符串，这样您就不会出现拼写错误。

完成后，您将获得：

=MID(A1,FIND("utm_source=",A1)+LEN("utm_source="),FIND("utm_medium=",A1)-(FIND("utm_source=",A1)+LEN("utm_source="))-1)

=MID(A1,FIND("utm_medium=",A1)+LEN("utm_medium="),FIND("utm_campaign=",A1)-(FIND("utm_medium=",A1)+LEN("utm_medium="))-1)

=MID(A1,FIND("utm_campaign=",A1)+LEN("utm_campaign="),FIND("utm_content=",A1)-(FIND("utm_campaign=",A1)+LEN("utm_campaign="))-1)

=MID(A1,FIND("utm_content=",A1)+LEN("utm_content="),FIND("utm_adsize=",A1)-(FIND("utm_content=",A1)+LEN("utm_content="))-1)

=MID(A1,FIND("utm_adsize=",A1)+LEN("utm_adsize="),FIND("utm_audience=",A1)-(FIND("utm_adsize=",A1)+LEN("utm_adsize="))-1)

=MID(A1,FIND("utm_audience=",A1)+LEN("utm_audience="),FIND("utm_product=",A1)-(FIND("utm_audience=",A1)+LEN("utm_audience="))-1)

=MID(A1,FIND("utm_product=",A1)+LEN("utm_product="),FIND("promocode=",A1)-(FIND("utm_product=",A1)+LEN("utm_product="))-1)

最后一个类似于第一个，只不过这次你要寻找的是字符串的右端。你想要的最后一部分是字符串的实际末端，因此你只需要确定起点和长度：

=RIGHT(A1,  LEN(A1) - (FIND("promocode=",A1) + LEN("promocode=") - 1))

好吧，您知道如何找到“promocode=”，因为您在第 10 个单元格中找到了它。如果您在这里找到它，您可以从整个字符串的长度中减去它（减 1，因为它的计算结果将给出您实际起始位置之前的位置），以获得您希望 Excel 从字符串末尾读取的字符长度。

这就是你处理所有这些事情的方法。如果你的字符串表现良好（假设你有 10,000 个这样的字符串，并且它们都包含相同的“utm_something”字符串），那么“就这么简单”。这是一项艰巨的工作，但请诚实地想想：一旦你知道如何去做，它只是一堆打字和鼠标操作，而不是一堆思考和做上百万种不同的事情。只是繁琐的工作，猴子的工作。

FILTERXML()如果字符串更加可预测，请记住我在开头提到的技巧。好吧，在所有位置使用相同的分隔符（“分隔符”）是完全可预测的。那么就非常简单了。

实际上，我们可以采取其他方法，但这些方法更难操作。而且... 很挑剔。

当您学会如何使用该LET()函数时，您可以使上面的公式看起来更易于阅读。并且更容易编辑不同的信息。

祝你使用 Excel 顺利！这是一款很棒的工具。希望你能用得愉快！

Answer 1