住所を数字で2分割する関数

Function SplitAddress(ByVal fullAddress As String) As Variant
    Dim address1 As String
    Dim address2 As String
    Dim i As Integer
    Dim isNumericFound As Boolean

    ' 数字がある位置から住所を分割
    For i = 1 To Len(fullAddress)
        If IsNumeric(Mid(fullAddress, i, 1)) Then
            isNumericFound = True
            Exit For
        End If
    Next i

    If isNumericFound Then
        ' 数字が見つかった場合
        address1 = Trim(Left(fullAddress, i - 1))
        address2 = Trim(Mid(fullAddress, i))
    Else
        ' 数字が見つからなかった場合
        address1 = Trim(fullAddress)
        address2 = ""
    End If

    ' 結果を配列に格納して返す
    SplitAddress = Array(address1, address2)
End Function

Sub TestSplitAddress()
    Dim result As Variant
    Dim fullAddress As String

    ' 住所を指定
    fullAddress = "東京都千代田区千代田 1-1 ●●ﾏﾝｼｮﾝA棟 1002号室"

    ' 住所を分割する関数を呼び出し
    result = SplitAddress(fullAddress)

    ' 結果を表示
    MsgBox "住所1: " & result(0) & vbCrLf & "住所2: " & result(1)
End Sub