在文本字符串中查找第一个仅由数字组成的字符串。仅公式

Question

是否可以在仅包含数字的字符串中找到第一个字符串？我有几千个地址需要处理，其中一些有公寓号。我需要找到地址号并提取它，但提取的却是公寓号。

我可以使用此公式提取第一个数字字符串，OneToZero 是 {1,2,3,4,5,6,7,8,9,0}

=MID($A1,MIN(IFERROR(FIND(OneToZero,$A1),"")),FIND(" ",$A1,MIN(IFERROR(FIND(OneToZero,$A1),"")))-MIN(IFERROR(FIND(OneToZero,$A1),""))-0)

返回此。

1417 Horne RD #1445A（15 号楼）-----1417(正确)
韦斯特伍德 #104A 801 坎特韦尔路 ---------104A(错误) 801(正确)

我尝试使用

=MID($A1,MIN(IFERROR(FIND(" "&OneToZero," "&$A1),"")),FIND(" ",$A1,MIN(IFERROR(FIND(" "&OneToZero," "&$A1),"")))-MIN(IFERROR(FIND(" "&OneToZero," "&$A1),""))-0)

但是如果第一个数字前面没有非数字字符，则返回此值。

韦斯特伍德 104A 801 坎特韦尔路 ---------104A(错误) 801(正确)

不能仅仅使用“#”删除第一个字符串，因为它可能显示“Apt#”或只是“104A”。

谢谢。

Answer 1

我发现这是为了测试字符串

=IF(AND(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))),A1,"")

并放置代码来定位第一个字符串和第二个字符串。

这是全部内容。我对此了解不多，只是拼凑了一些不同的小片段。我现在希望有人能帮助我简化这一点。

    =IF(AND(ISNUMBER(--(MID(

MID([@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")),FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")))-MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))-0),ROW(INDIRECT("1:"&LEN(MID([@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")),FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")))-MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))-0)))),1)))),

MID([@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")),FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")))-MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))-0),

MID([@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin],FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))+0)),"")),FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin],FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))+0)),""))),"")))-MIN(IFERROR(FIND(" "&OTZ," "&[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin],FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))+0)),""))),""))-0))

它很长。我将“OneToZero”从 {1,2,3,4,5,6,7,8,9,0} 改为 OTZ。[@Origin] 是“原始数据”（未格式化的地址）所在的列。

Answer 2

由于您要求提供 VBA 解决方案的示例，这将打印右侧单元格中第一个唯一的数字子字符串 -

Option Explicit

Public Sub ExtractStreetNumber()
    Application.ScreenUpdating = False
    Const nullCharacter As String = " "
    Dim subString As String
    Dim fullAddress As String
    Dim subStringArray() As String
    Dim arrayPosition As Long
    Dim testCell As Range
    Dim addressTestRange As Range
    Set addressTestRange = Range("A1:A3") 'put your range of addresses here

    For Each testCell In addressTestRange
        fullAddress = testCell
        subStringArray = Split(fullAddress, nullCharacter)
        For arrayPosition = 0 To UBound(subStringArray)
            subString = subStringArray(arrayPosition)
            If IsNumeric(subString) Then
            testCell.Offset(, 1) = subString
            GoTo NextIteration
            End If
        Next
NextIteration:
    Next
    Application.ScreenUpdating = True
End Sub

您还可以创建一个用户定义的函数在工作表上使用，例如ExtractStreetNumber(A1)-

Option Explicit

Public Function ExtractStreetNumber(ByVal fullAddress As String) As Long

    Const nullCharacter As String = " "
    Dim subString As String
    Dim subStringArray() As String
    Dim arrayPosition As Long

        subStringArray = Split(fullAddress, nullCharacter)
        For arrayPosition = 0 To UBound(subStringArray)
            subString = subStringArray(arrayPosition)
            If IsNumeric(subString) Then
                ExtractStreetNumber = subString
            Exit Function
            End If
        Next

End Function

Answer 3

这是使用公式的另一种方法，但你需要将文本添加到列中，而我公然抄袭了斯科特的回答回答另一个问题。

首先，我将数据复制到新表。

去data - data tools - text to columns - delimited - space

现在，你可以使用类似以下内容找到每行中第一个唯一的数字子字符串

=INDIRECT(ADDRESS(ROW(),MIN(IF(ISNUMBER(A1:F1),COLUMN(A1:F1)))))

这是一个数组公式，因此您需要使用Ctrl++并确保它带有花括号。ShftEntr

现在您可以将数字复制粘贴回原始工作表。