在Excel中查找字符的第N个实例(无VBA) [英] Find the Nth instance of a character in Excel (no VBA)

问题描述

TL; DR摘要:我想要一个将在字符串中找到第N个"_"(对于任何N)并返回其索引的公式；或找到第N个子字符串，用"_"分隔.我有VBA来执行此操作，但是速度很慢.

TL;DR summary: I want a formula that will find the Nth "_" (for any N) in a string, and return its index; OR to find the Nth substring, separated by "_". I have VBA to do this, but it's slow.

长版: 我正在处理广告活动数据.我的营销人员(很幸运)为他们的广告系列使用了一致的命名方案.不幸的是，这很长.

Long version: I am working with advertising campaign data. My marketers (fortunately) use a consistent naming scheme for their campaigns. Unfortunately, it's very long.

广告系列名称仅包含我无法通过报告获得的1条数据.

The campaign names contain exactly 1 piece of data that I cannot otherwise get from reports.

供参考，广告系列名称的格式为:

For reference, campaign names are of the format:

ADV_CO_BG_Product_UniqueID_XX_mm.dd.yyyy_mm.dd.yyyy_TYP_NUM

...我有一列约200K(每周增长几百).

... and I have a column of about 200K of them (growing by a couple hundred each week).

重要的是，广告系列名称包含多个部分，其中_是它们之间的分隔符.在这种情况下，我需要第9部分，但我需要一个足够灵活的选项，无需添加或删除行即可更改我要定位的部分.

The important part is that there are multiple parts of the campaign name, with _ as a delimiter between them. In this case, I want the 9th part, but i want an option that is flexible enough that I don't have to add or remove lines to change which part I target.

我在其他问题上也看到过使用嵌套公式，例如:

I've seen on other questions to use a nested formula like:

=MID(
  Data_OLV[@Campaign],
  FIND("_",Data_OLV[@Campaign],
    FIND("_",Data_OLV[@Campaign],
      FIND("_",Data_OLV[@Campaign],
        FIND("_",Data_OLV[@Campaign],
          FIND("_",Data_OLV[@Campaign],
            FIND("_",Data_OLV[@Campaign],
              FIND("_",Data_OLV[@Campaign],
                FIND("_",Data_OLV[@Campaign])+1)
              +1)
            +1)
          +1)
        +1)
      +1)
    +1)
  +1,
3)

...但是如果我需要其他位置的东西很难修改.

... but that is hard to modify if I need something in a different position.

我有一个称为StringSplit的UDF(请参见下文)，它提供了所需的结果，但是它非常慢(并且只有在启用了宏的情况下才起作用，但并不是我的所有听众都这么做).

I have a UDF called StringSplit (see below) that provides the desired results, but it's extremely slow (and only works if you enable macros, which not all of my audience does).

是否有更好的方法来做我想做的事情?

Is there a better way to do what I'm trying to do?

    Public Function StringSplit(input_ As String, delimiter_ As String, index_ As Integer)
        On Error GoTo err

        out = Split(input_, delimiter_, -1, vbTextCompare)
        StringSplit = out(index_ - 1)
        Exit Function
    err:
        If err.Number = 9 Then
            StringSplit = CVErr(xlErrRef)
            Exit Function
        End If
        StringSplit = err.Description
    End Function

推荐答案

我认为这是您要寻找的公式-

I think this is the formula you are looking for -

=MID(A2, FIND(CHAR(1), SUBSTITUTE(A2, B2, CHAR(1), C2))+1, FIND(CHAR(1), SUBSTITUTE(A2, B2, CHAR(1), C2+1)) -  FIND(CHAR(1), SUBSTITUTE(A2, B2, CHAR(1), C2))-1)

这是怎么做-

此处B2是Delimiter type，C2是Nth occurrence of the Delimiter.您可以根据需要修改代码.只需更改B2& C2.

Here B2 is the Delimiter type and C2 is the Nth occurrence of the Delimiter. you can modify the code as per your need. Just change the B2 & C2.

这篇关于在Excel中查找字符的第N个实例(无VBA)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！