excel - 从 Excel 单元格中提取固定长度的数字

Question

一些类似命名的线程，但仍然无法解决我的问题。我需要从 Excel 字符串中提取一个固定长度的 NUMBER 值(在我的场景中为 8 位)。为此目的提供了以下 Excel 公式:

=MID(A1,FIND("--------",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0","-"),"1","-"),"2","-"),"3","-"),"4","-"),"5","-"),"6","-"),"7","-"),"8","-"),"9","-")),8)

它可以完成这项工作，但是我对此有两个问题:

最重要的是 - 我正在寻找 完全匹配的 匹配项。虽然它确实提取了它找到的第一个 8 位序列，但我真的只在 之后 8 位数字，这意味着应该忽略 9 位(或更长)数字(因为 7 位数字已经是)。此公式还从较长的数字中提取前 8 位数字。

不太重要，但最好只查找以 1 开头的数字。所以，真的只是想提取这个:1???????作为数值。所以应该提取像“a 12891212 a”或“a 12891212 a”之类的东西，同时应该提取 12891212 0a 或 2 2。

如果合理可行，与 VBA 相比，我更喜欢基于 Excel 公式的方法。
任何帮助深表感谢!

Answer 1

这可以通过公式完成，但完全取决于您的 Excel 版本:


1) Excel 2016，您仍然可以使用公式:B1 中的公式:

=IFERROR(MID(A1,MAX((MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)="1")*(ISNUMBER(--MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),8)))*(NOT(ISNUMBER(--MID(A1,ROW(A$1:INDEX(A:A,LEN(A1)))+8,1))))*(NOT(ISNUMBER(--MID(A1,ROW(A$1:INDEX(A:A,LEN(A1)))-1,1))))*(ROW(A$1:INDEX(A:A,LEN(A1))))),8),"Nothing found")

注: 这是一个数组公式，需要通过CtrlShiftEnter确认

2) Excel 2019，使用 CONCAT() 和 FILTERXML() : B1 中的公式:

=IFERROR(FILTERXML("<t><s>"&CONCAT(IF(ISNUMBER(--MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)),MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1),"</s><s>"))&"</s></t>","//s[starts-with(., '1') and string-length(.) =8]"),"Nothing Found")

注: 这是一个数组公式，需要通过CtrlShiftEnter确认

3) Excel 365，使用前面提到的函数，但包括 SEQUENCE() : B1 中的公式:

=IFERROR(FILTERXML("<t><s>"&LET(X,MID(A1,SEQUENCE(LEN(A1)),1),CONCAT(IF(ISNUMBER(--X),X,"</s><s>")))&"</s></t>","//s[starts-with(., '1') and string-length(.) =8]"),"Nothing Found")

公式的 XPATH 部分负责实际查询，查找以“1”开头且总长度为“8”的字符串。然后，这甚至可以使用返回 '12345678' 之类的字符串，例如 'abc123456789abc12345678abc29876543'。
如果您喜欢 FILTERXML 和 XPATH ，那么您可能会发现 this 很有趣。

4) Excel 365，内部版本(撰写本文时)使用 TEXTSPLIT() :

=LET(X,MID(A1,SEQUENCE(LEN(A1)),1),Y,TEXTSPLIT(A1,IF(ISNUMBER(--X)," ",X),,1),FILTER(Y,(--LEFT(Y)=1)*(LEN(Y)=8),"Nothing Found"))

5) VBA:如果你必须使用 VBA，我想 UDF 是一个不错的选择。就像是:

Function GetStr(str As String, pat As String) As String

With CreateObject("vbscript.regexp")
    .Pattern = pat
    .Global = True
    If .Test(str) = True Then
        GetStr = .Execute(str)(0).Submatches(0)
    Else
        GetStr = "Nothing found"
    End If
End With

End Function

您可以按照 B1 在 =GetStr(A1,"(?:^|\D)(1\d{7})(?:\D|$)") 中调用它。这是使用正则表达式。如果您有兴趣并想了解更多信息，那么 this 对您来说是一本有趣的读物。
我故意将模式留在 UDF 之外，您可能想要更改它。当前模式可以在这个在线 Demo 中看到，引擎将从左到右查找:

(?: - 第一个非捕获组

^|\D - 起始字符串 ancor 或除数字以外的任何内容。

) - 关闭第一个非捕获组。

( - 第一个捕获组。

1\d{7} - 搜索文字 1 后跟 7 位数字。

) - 关闭第一个捕获组。

(?: - 第二个非捕获组

\D|$ - 除数字或结束字符串 ancor 之外的任何内容。

) - 关闭第二个非捕获组。