从不同长度/格式的单元格中提取数字

I am looking for a formula to extract digits from column A. The problem I am running into is the varying lengths and formats. I am seeking a solution based on the desired result column below.

我正在寻找一个从A列中提取数字的公式。我遇到的问题是不同的长度和格式。我正在寻找基于下面所需结果列的解决方案。

Formula in Column B:

B栏中的公式：

=IFERROR(INT(LEFT(REPLACE(SUBSTITUTE(A4,"-"," "),1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A4&1/17))-1,""),5)),INT(LEFT(REPLACE(SUBSTITUTE(A4,"-"," "),1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A4&1/17))-1,""),4)))

Workbook:

工作簿：

Thank you!

谢谢！

3 个解决方案

#1

Try the following User Defined Function:

尝试以下用户定义的功能：

Public Function LastNumber(s As String) As Variant
    Dim L As Long, i As Long, temp As String, arr
    Dim CH As String

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

    arr = Split(Application.WorksheetFunction.Trim(temp), " ")
    For i = UBound(arr) To LBound(arr) Step -1
        If IsNumeric(arr(i)) Then
            LastNumber = CLng(arr(i))
            Exit Function
        End If
    Next i
    LastNumber = ""

End Function

It will return the last number (set of numerals) in a string.

它将返回字符串中的最后一个数字（数字集）。

User Defined Functions (UDFs) are very easy to install and use:

用户定义函数（UDF）非常易于安装和使用：

ALT-F11 brings up the VBE window
ALT-F11调出VBE窗口
ALT-I ALT-M opens a fresh module
ALT-I ALT-M打开一个新模块
paste the stuff in and close the VBE window
粘贴内容并关闭VBE窗口

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

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

To remove the UDF:

要删除UDF：

bring up the VBE window as above
如上所述调出VBE窗口
clear the code out
清除代码
close the VBE window
关闭VBE窗口

To use the UDF from Excel:

要从Excel使用UDF：

=myfunction(A1)

= myfunction的（A1）

To learn more about macros in general, see:

and

和

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

and for specifics on UDFs, see:

有关UDF的详细信息，请参阅：

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

Macros must be enabled for this to work!

必须启用宏才能使其正常工作！

#2

Try this user defined function in a standard public module code sheet.

在标准公共模块代码表中尝试此用户定义函数。

function lastNumber(str as string)
    dim i as long, tmp as string

    tmp = str

    for i=len(tmp) to 1 step-1
        if asc(right(tmp, 1))<48 or asc(right(tmp, 1))>57 then
            tmp = left(tmp, len(tmp)-1)
        else
            exit for
        end if
    next i

    for i=len(tmp) to 1 step-1
        if asc(mid(tmp, i))<48 or asc(mid(tmp, i))>57 then
            exit for
        end if
    next i

    lastNumber = mid(tmp, i+1)
end function

#3

You could use Regex, which needs a reference to Microsoft VBScript Regular Expressions:

您可以使用Regex，它需要引用Microsoft VBScript正则表达式：

Public Function GetLastNum(str As String) As String
    Dim numRegEx As RegExp
    Set numRegEx = New RegExp

    Dim matchColl As MatchCollection
    Dim retStr As String
    With numRegEx
        .Global = True
        .Pattern = "[0-9]+"
        Set matchColl = .Execute(str)
        If matchColl.Count > 0 Then
            GetLastNum = matchColl.Item(matchColl.Count - 1)
            Exit Function
        End If
    End With
End Function

#1