Найти первую строку в текстовой строке, которая является только числами. Только формулы

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 (правильно)
Westwood # 104A 801 Cantwell Ln --------- 104A (неверно) 801 (правильно)

Я пытался с помощью

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

Но возвращает это, если первое число не имеет перед собой нецифровый символ.

Westwood 104A 801 Cantwell Ln --------- 104A(неверно) 801(правильно)

Невозможно просто удалить первую строку, используя «#», потому что она может сказать «Apt #» или просто "104A".

Спасибо.

Answer 1

Поскольку вы запросили пример решения 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 2

Я нашел это, чтобы проверить строку

=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 3

Вот еще один способ сделать это с формулой, но вам нужно написать текст в столбцы, и я откровенно срываю ответ Скотта на другой вопрос.

Сначала я скопировал бы данные на новый лист.

Перейти к data - data tools - text to columns - delimited - space

Теперь вы можете найти первую только числовую подстроку в каждой строке с чем-то вроде

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

Это формула массива, поэтому вам нужно использовать Ctrl+Shft+Entr и убедиться, что она имеет фигурные скобки.

Теперь вы можете скопировать - вставить специальный номер обратно на исходный лист.