我正在尝试从几个包含大量文本的单元格中提取一些数字。
数字字符串伴随着我试图提取的数字字符串之前的某些单词。
我尝试使用 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) 非常容易安装和使用:
- ALT-F11 打开 VBE 窗口
- ALT-I ALT-M 打开新模块
- 粘贴内容并关闭 VBE 窗口
如果您保存工作簿,UDF 将随之保存。如果您使用的是 2003 之后的 Excel 版本,则必须将文件保存为 .xlsm 而不是 .xlsx
要删除 UDF:
- 调出如上所示的 VBE 窗口
- 清除代码
- 关闭 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 为目标。