トムはたくさんの住所リストを扱っています。彼を夢中にさせる1つのことは、番地が1つのセルにある場合です。トムは、あるセルに家番号を、別のセルに通りを必要としています。したがって、アドレスが「1234 Maple Glen Ave.」の場合、1つのセルに「1234」と「MapleGlenAve。」が必要です。別の。彼は家の番号を通りの名前の前に配置された列にコピーできますが、番号を再入力してから通りの名前から削除するのは長いプロセスです。トムは、通りの名前から家番号を「分割」するもっと簡単な方法があるかどうか疑問に思います。

このタスクを実行するには、いくつかの方法があります。 （リボンの[データ]タブにある）[テキストから列へ]ツールを使用すると考える人もいるかもしれません

望ましい結果が得られます。残念ながら、それは家番号を分離するための最善の方法ではありません。理由は単純です。区切り文字を指定してツールを使用する場合、意味のあるのはスペースだけです。しかし、そうすると、「1234 MapleGlenAve」になってしまいます。目的の2列ではなく4列に分散されます。次に、通りの名前を貼り付ける方法を考え出す必要があります。

Text to Columnsツールを使用する可能性のあるもう1つの方法は、（区切り文字の代わりに）固定の列幅を使用することです。このアプローチは、すべての住所の番地の桁数が同じである場合にのみ機能します。もちろん、ほとんどのデータセットでは、これは当てはまりません。家番号は、ほぼ任意の桁数である可能性があり、数字以外の文字（ “1234A”、 “1234-B”、または「1234-36」）。

うまくいく1つのアプローチは、数式を使用して元のアドレスを分解することです。数式は、住所の最初のスペースを入力して、スペースの左側の部分または右側の部分を返すことができます。セルA1のアドレスを指定して、スペースの前にあるすべてのものを引き出す方法は次のとおりです。

=LEFT(A1,FIND(" ",A1)-1)

アドレスに数字以外の文字が含まれないことが確実な場合は、式をVALUE関数でラップして、家番号を数値として使用することができます。

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

最初のスペースに続くアドレスの部分を取得するには、次の式を使用できます。

=MID(A1,FIND(" ",A1)+1,LEN(A1))

必要に応じて、最初の式で引き出した家番号を参照する式を考案することもできます。たとえば、家番号の数式が列Bにあるとします。列Cに次のように配置できます：

=TRIM(SUBSTITUTE(A1,B1,))

列BとCの数式（および列Aのアドレス）を使用すると、必要な行数だけ数式をコピーできます。次に、そのB：C範囲を選択し、[形式を選択して貼り付け]を使用して値をそれらのセルに貼り付けることができます。これを行った後、列Aの元のアドレスを安全に削除できます。

多くの場合、多くのアドレスを実行する必要がある場合は、マクロを使用して引き離す方がよい場合があります。次のサンプルマクロは、実行時に選択した範囲で機能します。選択範囲の左側に空白セルの列を挿入し、それらのセルに家番号を詰め込んでから、家番号が含まれないように住所を調整します。

Sub SplitAddress()

Dim c As Range     Dim j As Integer     Dim n As String     Dim addr As String

Selection.Insert Shift:=xlToRight     Selection.Offset(0, 1).Select

For Each c In Selection         j = InStr(1, c, " ")

n = Left(c, j)

c.Offset(0, -1) = n         addr = Trim(Right(c, Len(c) - j))

c = addr     Next End Sub

必要に応じて、家番号だけを返すユーザー定義関数を作成できます。

Function GrabHouseNumber(Raw As String) As Text     Dim x As Variant     Dim House As Variant

x = Split(Raw, " ")     'use space char to split elements into array     House = x(0)   'first element of array     If Left(House, 1) Like "#" Then         'First char is numeric digit         GrabHouseNumber = House  'set return value as house number     Else         GrabHouseNumber = ""  'First char is text, so not a house number     End If End Function

ユーザー定義関数を使用する利点は、元のアドレスの最初の部分が実際に数字で始まっているかどうかを確認することです。そうでない場合は、住所が家番号で始まっていないものと見なされます。 （家番号全体は数字である必要はありません。数字で始まる必要があります。）

注：

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

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

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

このヒント（13350）は、Microsoft Excel 2007、2010、2013、2016、2019、およびOffice365のExcelに適用されます。