アランには数百の名前と住所のリストがあります。番地の範囲は、メインストリート、123メインストリート、US RT 2、または187サウスエルムストリートです。彼は、住所から番地を分類したいと考えています。

したがって、アドレス123 Main Streetは、あるセルに「123」、別のセルに「MainStreet」になります。番地がない場合、番地の列には何も表示されません。 Text to Columnsツールは機能せず、どうすればこれを実行できるのか疑問に思います。

完璧な世界では、Excelを使用すると、通りの名前から番号を簡単に分割できます。このオプションは存在しないため、いくつかの選択肢があります。最も時間のかかるオプションは、列を追加してデータを再入力することです。ただし、時間を節約したい場合は、さまざまな数式を使用してタスクを実行できます。

アドレスのリストが列A（セルA1で始まる）にあるとすると、次のような数式を使用して、アドレスの数値部分を引き出すことができます。

=IF(ISERROR(VALUE(LEFT(A1,1))),"",LEFT(A1,FIND(" ",A1)-1))

セルB1に数式を入力すると、別の数式を使用して、アドレスの非数値部分を導出できます。

=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

このアプローチには制限があることに注意してください。一部の住所、特に主要な大都市圏では、152-33 BellBlvdなどの形式を使用しています。上記の式はこれらのアドレスで機能しますが、代替の152 33 Bell Blvd.を使用すると、式が正しく解析されません。専門的に開発された住所解析プログラムを購入したい場合を除いて、上記の式と結果の簡単な眼球スキャンで十分です。

この場合、別の式が機能します。アドレスがセルA2にあると仮定して、セルB2に次の数式を入力します。

=IF(ISNUMBER(VALUE(LEFT(A2,1))),VALUE(LEFT(A2,FIND(" ",A2)-1)),"")

この数式は、「最初の文字が数字でない場合は、セルを空白のままにします。それ以外の場合は、最初のスペースを含めずに、左側のすべての文字を入力してください」と言っています。次に、この式の結果を使用して、アドレスの数値以外の部分を引き出すことができます。

=IF(B2="",A2,MID(A2,FIND(" ",A2)+1,99))

別のアプローチは、配列数式を使用することです。ここでも、アドレスがセルA2にあると仮定すると、次を使用できます。

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1)) = TRUE,LEFT(A2,FIND(" ",A2,1)),"")

これは配列数式なので、Ctrl + Shift + Enterを使用して入力する必要があります。その結果、数式はアドレスの先頭の数値部分を返します。次に、次の配列数式を使用して、数値以外の部分を判別できます。

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1))=TRUE,RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),A2)

最後に、次のマクロを使用して、番地名から番地を分類できます。

Sub GetStreetNum()

Dim sStreet As String     Dim J As Integer     Dim iNum As Integer

For Each cell In Selection         sStreet = cell.Value         J = InStr(sStreet, " ")

If J > 0 Then             iNum = Val(Left(sStreet, J))

If iNum > 0 Then                 cell.Offset(0, 1).Value = iNum                 sStreet = Trim(Mid(sStreet, J, Len(sStreet)))

End If         End If         cell.Offset(0, 2).Value = sStreet     Next End Sub

このマクロを使用するには、アドレスを含むセルの範囲を選択して実行するだけです。アドレスの先頭の数値部分は各アドレスの右側のセルに表示され、アドレスの残りはその右側のセルに配置されます。 （したがって、選択したアドレスの右側に2つの空白の列があることを確認する必要があります。）

注：

このページ（または_ExcelTips_サイトの他のページ）で説明されているマクロの使用方法を知りたい場合は、役立つ情報を含む特別なページを用意しました。

linkここをクリックして、新しいブラウザタブでその特別なページを開きます。

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント（8031）は、Microsoft Excel 2007、2010、2013、2016、2019、およびOffice 365のExcelに適用されます。Excelの古いメニューインターフェイス用のこのヒントのバージョンは、次の場所にあります。

link住所から番地を抽出。