Excel - 如何返回大段文本中长度在 7-10 个字符之间的第一个数字字符串?(最好仅使用公式)

Excel - 如何返回大段文本中长度在 7-10 个字符之间的第一个数字字符串?(最好仅使用公式)

我正在尝试从几个包含大量文本的单元格中提取一些数字。

数字字符串伴随着我试图提取的数字字符串之前的某些单词。

我尝试使用 MID、LEFT、RIGHT、LEN、FIND 和 SEARCH 等函数解决问题,但最终总是得到错误的结果。

这是由于文本主体存在三个问题:

  • 第一个问题是,每个单元格前面的单词都不同。这会使公式变得非常复杂,需要 IF、OR 或 AND 函数。
  • 第二个问题是我尝试提取的数字字符串的长度在 7 到 10 个数字之间变化
  • 第三个问题是,我尝试提取的数字字符串不是单元格正文中唯一的数字字符串

我目前的解决方案是,如果数字字符串长度少于 10 个数字,则添加除数字以外的其他字符;例如空格、逗号和括号。

所以基本上我想知道是否有办法从文本主体中提取第一个长度在 7-10 个字符之间的数字字符串?最好只使用公式,但 VBA 也是可能的。


由于我还没有收到回复,我想我会用数据示例来编辑 OP。

我尝试操作的数据的一个示例可以在这里找到: https://www.sendspace.com/file/f7kn6n


由于我有一段时间没有收到回复,所以我想我会用几天前上传的示例数据的屏幕截图来更新。

示例数据

答案1

如果您的数字串始终是字符串中的第一组数字,则可以使用以下公式。这是一个数组公式,通过按住ctrl+shift并点击 来输入enter

=MAX(IFERROR(--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{7,8,9,10}),0))

如果在您想要提取的数字子串之前可能存在更短或更长的数字子串,那么我会使用下面的 UDF。它利用正则表达式来查找长度恰好为 7-10 位的第一个数字字符串。由于它返回的是字符串,因此它应该保留所有前导零。

在如下公式中使用它:

=FirstDigits(A1)

将以下代码复制到常规模块中:

Option Explicit
Function FirstDigits(S As String) As String
    Dim RE As Object, MC As Object
    Const sPat As String = "\b\d{7,10}\b"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = sPat
    If .test(S) Then
        Set MC = RE.Execute(S)
        FirstDigits = MC(0)
    Else
        FirstDigits = "No digit string 7-10 digits long"
    End If
End With

End Function

答案2

如果您收到符合您要求的“仅公式”答案,请忽略此答案。



这个小UDF()将返回满足您要求的字符串中的第一个数字:

在此处输入图片描述

代码:

Public Function GetNumber(sIN As String) As Long
    Dim L As Long, i As Long
    Dim s As String

    s = sIN
    L = Len(s)
    For i = 1 To L
        ch = Mid(s, i, 1)
        If ch Like "[0-9]" Then
        Else
            Mid(s, i, 1) = " "
        End If
    Next i

    With Application.WorksheetFunction
        arr = Split(.Trim(s), " ")
    End With

    For Each a In arr
        If Len(a) > 6 And Len(a) < 11 Then
            GetNumber = CLng(a)
            Exit Function
        End If
    Next a
    GetNumber = 0
End Function

用户定义函数 (UDF) 非常容易安装和使用:

  1. ALT-F11 打开 VBE 窗口
  2. ALT-I ALT-M 打开新模块
  3. 粘贴内容并关闭 VBE 窗口

如果您保存工作簿,UDF 将随之保存。如果您使用的是 2003 之后的 Excel 版本,则必须将文件保存为 .xlsm 而不是 .xlsx

要删除 UDF:

  1. 调出如上所示的 VBE 窗口
  2. 清除代码
  3. 关闭 VBE 窗口

要从 Excel 使用 UDF:

=getnumber(A1)

要了解有关宏的更多信息,请参阅:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

有关 UDF 的详细信息,请参阅:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

必须启用宏才能使其工作!

答案3

这是我认为有效的一个公式。

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), 
    ((1/MAX(IFERROR(1/(
        ISNUMBER((TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (ROW($1:$25)-1)*LEN(A1)+1, LEN(A1))))*1)*
        (LEN((TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (ROW($1:$25)-1)*LEN(A1)+1, LEN(A1)))))>=7)*
        (LEN((TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (ROW($1:$25)-1)*LEN(A1)+1, LEN(A1)))))<=10)*
        (ROW($1:$25))),-1)))-1)*LEN(A1)+1, LEN(A1)))

说实话,一旦它变成这种格式,我就无法解释它了。它开始这里给出了提取第 n 个单词的公式

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1)))

然后我必须让它检查所有的单词,所以在 N 的位置添加了 ROW($1:$25)(只给出数组 1:25)。

然后我必须检查它是否是一个数字(是数字),检查长度(两个长度>=7长度<=10函数,将其乘以数组 1:25。

然后我需要提取非 0 的最小数字,我用1/最大(如果错误(1/公式。如果不满足条件,则返回值为 0,1/0 错误,因此分配为 -1。其他数字为 1/N,取最大值,然后再次反转,得到最小数字,而不是 0。通过该数字返回上面的原始等式以返回该单词。

目前它仅适用于字符串的前 25 个单词(可以扩展 Row(1:25))。它以单元格 A1 为目标。

相关内容