vba - 从一列中提取 5 位数字到另一列

Question

我需要帮助在 Excel 2010 中仅从一列到另一列提取 5 位数字。这些数字可以位于字符串的任何位置(字符串的开头、中间的任何位置或结尾)。它们可以在括号或引号内，例如:

(15478) or "15478" or '15478' or [15478]

我需要忽略任何小于 5 位的数字，包括以 1 个或多个前导零开头的数字(如 00052、00278 等)，并确保将前导零复制到下一列。有人可以帮助我创建公式或 UDF 吗？

Answer 1

这是一个基于公式的替代方案，它将提取单元格 A1 中的前 5 位数字。在大多数情况下，我倾向于使用相当简单的公式解决方案而不是 VBA，因为公式更便携。此公式为数组公式，因此必须使用 Ctrl+Shift+Enter 输入。这个想法是将字符串分成每个可能的 5 个字符 block 并测试每个 block 并返回第一个匹配项。

=MID(A1,MIN(IF(NOT(ISERROR(("1"&MID(A1,ROW(INDIRECT("R1C[1]:R"&(LEN(A1)-4)&"C[1]",FALSE)),5)&".1")*1))*ISERROR(MID(A1,ROW(INDIRECT("R1C[1]:R"&(LEN(A1)-4)&"C[1]",FALSE))+5,1)*1)*ISERROR(MID(A1,ROW(INDIRECT("R1C[1]:R"&(LEN(A1)-4)&"C[1]",FALSE))-1,1)*1),ROW(INDIRECT("R1C[1]:R"&(LEN(A1)-4)&"C[1]",FALSE)),9999999999)),5)

让我们分解一下。首先，我们有一个表达式，我使用了两次来返回一个从 1 到 4 的数字数组，比初始文本的长度小。因此，如果您有一个长度为 10 的字符串，则以下将返回 {1,2,3,4,5,6}。以下公式将被称为行列表。我使用 R1C1 符号来避免潜在的循环引用。

ROW(INDIRECT("R1C[1]:R"&(LEN(A1)-4)&"C[1]",FALSE))

接下来，我们将使用该数组将文本拆分为 5 个字母 block 的数组并测试每个 block 。正在执行的测试是在前面加上“1”并附加“.1”，然后验证 block 是数字的。前置和附加消除了空格或小数的可能性。然后我们可以检查之前的字符和之后的字符，以确保它们不是数字。下面的公式将被称为 isnumarray。

NOT(ISERROR(("1"&MID(A1,rowlist,5)&".1")*1))
*ISERROR(MID(A1,rowlist+5,1)*1)
*ISERROR(MID(A1,rowlist-1,1)*1)

接下来，我们需要通过从 rowlist 公式的副本返回当前索引并为不匹配返回一个大数字来找到字符串中第一个有效的 5 位数字。然后我们可以使用 MIN获取第一场比赛的功能。以下将被称为minindex。

MIN(IF(isnumarray,rowlist,9999999999))

最后，我们需要获取从 MIN 返回的索引开始的数字字符串。功能。

MID(A1,minindex,5)