住所を町名までと番地を分けて表示させたい
Excelで住所データを町名までと番地に分けて表示させる方法について解説します。例えば「東京都港区港町2-52-1」という住所を「東京都港区港町」と「2-52-1」に分けて表示する方法です。この数式は全角数字にも対応しているため、半角に変換する手順は不要です。
手順
町名までの住所にはアラビア数字（１、２、３などのこと）が用いられないことを利用します。
- 住所データが入力されているセル（例：A2）を選択します。
- 町名部分を抽出する数式を別のセル（例：B2）に町名までの部分を抽出する数式を入力します。
- 番地部分を抽出する数式を別のセル（例：C2）に番地部分を抽出する数式を入力します。
分離の考え方
| 町名まで | 番地 |
| 東京都港区港町 | 2-52-1 |
| 数字が始まるまでの位置 | 数字が始まる位置 |
数式の解説
最初に出現する数字の位置を基準に、町名部分と番地部分を分割します。以下に、2つの数式を詳細に解説します。
町名までの部分を抽出する数式
住所から「町名まで」を抽出するには、次の数式を使用します。セルA2に住所が入っている場合：
=LEFT(A2, MIN(IF(ISNUMBER(VALUE(MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1))), ROW(INDIRECT(“1:”&LEN(A2))))) – 1)
数式の詳細：
- ROW(INDIRECT(“1:”&LEN(A2))): セルA2の文字数を基に、1からその長さまでの数値配列を生成します。これにより、各文字の位置を表す配列が作られます。
- MID(A2, ROW(…), 1): ROW関数で取得した各文字の位置を使って、A2の各文字を1文字ずつ抽出します。
- VALUE(…): 抽出された文字を数値に変換し、数値でない場合はエラーを返します。
- ISNUMBER(…): VALUE関数の結果が数値であればTRUEを返し、そうでなければFALSEを返します。
- MIN(IF(…)): TRUEとなる最初の数値の位置を特定し、最小の位置を取得します。この位置が最初の数字の位置です。
- LEFT(A2, … – 1): A2の先頭から、最初の数字の1つ前の位置までを抽出し、町名部分を取り出します。
番地部分を抽出する数式
番地部分を抽出するには、次の数式を使用します：
=MID(A2, MIN(IF(ISNUMBER(VALUE(MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1))), ROW(INDIRECT(“1:”&LEN(A2))))), LEN(A2))
数式の詳細：
- MIN(IF(…)): 町名部分を抽出する数式と同様に、最初の数字の位置を特定します。
- MID(A2, …, LEN(A2)): 最初の数字の位置から始めて、A2の末尾までを抽出します。これにより、番地部分が取得されます。
例
以下の表で、A列に元の住所データが入力されており、B列に町名、C列に番地が分割表示されています。
| A | B | C | |
|---|---|---|---|
| 1 | 住所 | 町名まで | 番地 |
| 2 | 東京都港区港町2-52-1 | =LEFT(A2, MIN(IF(ISNUMBER(VALUE(MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1))), ROW(INDIRECT(“1:”&LEN(A2))))) – 1) | =MID(A2, MIN(IF(ISNUMBER(VALUE(MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1))), ROW(INDIRECT(“1:”&LEN(A2))))), LEN(A2)) |
結果
- B2に「東京都港区港町」
- C2に「2-52-1」
まとめ
Excelで住所を町名までと番地に分けるには、最初の数字の位置を基準に分割する数式が有効です。この数式は全角数字にも対応しているため、データの変換が不要で、効率的に住所データを管理できます。
使用した関数について
IF関数で論理式による表示の切り替えの方法についてわかりやすく解説
IF関数についてIFの概要論理式の結果によって表示を切り替えるExcel関数=IF( 論理式 , TRUEの場合 , FALSEの場合 )概要 論理式の結果によって異なる値を返す 論理式については論理式を参照 文字列を比較し等しければTRUEを返すEXACT、数値を比較するDELTAを用いて正誤チェックなどをすることも...
programming-cafe.com
INDIRECT関数で数値かどうかを判定する方法についてわかりやすく解説
INDIRECT関数についてINDIRECTの概要文字列で指定された参照を返すExcel関数=INDIRECT( 参照 )概要 INDIRECT関数は、指定された文字列をセル参照に変換し、そのセルの値を返します。間接的にセルや範囲を参照するため、動的な範囲指定に便利です。 INDIRECT関数は、セル参照を動的に扱いた...
programming-cafe.com
ISNUMBER関数で数値かどうかを判定する方法についてわかりやすく解説
ISNUMBER関数についてISNUMBERの概要値が数値かどうかを判定Excel関数=ISNUMBER( 値 )概要 ISNUMBER関数は、指定したセルや値が数値かどうかを判定し、数値であればTRUE、そうでなければFALSEを返します。 ISNUMBER関数は、数値かどうかを判定する簡単な方法を提供します。 エラ...
programming-cafe.com
LEFT関数で指定した数の文字列を先頭から取得する方法と日付処理やRIGHTと組合せ方法についてわかりやすく解説
LEFT関数とLEFTB関数についてLEFTとLEFTBの概要文字列を先頭から取り出すExcel関数=LEFT( 文字列 , 文字数 )=LEFTB( 文字列 , 文字数 )概要 指定した文字数分の文字を先頭(左側)から取り出す 文字列の先頭から、文字数分の位置までの文字列を取り出す 末尾から取り出す関数はRIGHTで...
programming-cafe.com
LEN関数で文字数をカウントする方法と複数セルの文字数取得や空白処理についてわかりやすく解説
LEN関数とLENB関数についてLENの概要文字数をカウントExcel関数=LEN( 文字列 )=LENB( 文字列 )概要 文字数をカウントする関数で、LENBは全角文字(2バイト文字)を2と判定します 半角カナでｶﾞﾀﾞﾊﾞﾊﾟなどは、濁点を1文字として扱うため、ﾀﾞはLENであっても2文字とカウントされる。ﾀﾞ(...
programming-cafe.com
MID関数で文字列の取り出す方法と右から取り出す方法や応用テクニックをわかりやすく解説
MID関数とMIDB関数についてMIDとMIDBの概要指定位置から文字列を取り出すExcel関数=MID( 文字列 , 開始位置 , 文字数 )=MIDB( 文字列 , 開始位置 , 文字数 )概要 開始位置から指定した文字数分、文字列を取り出す 指定した文字以降の文字を取り出すには、指定した文字の位置をSEARCHで...
programming-cafe.com
MIN関数でデータの最小値を求める方法と0以外や2番目の最小値を求める方法についてもわかりやすく解説
MIN関数についてMINの概要データの最小値Excel関数=MIN( 数値1 , 数値2 ,,, )概要 指定したデータの最小値を求める MINは空白文字、論理値などを無視しますが、MINAは空白セルのみ無視しますMINはどのような時に使用するか データの中で最小の値を求めたいMINの使い方 ABCDE1 21...
programming-cafe.com
ROW関数で行番号を取得する方法とROWSとの比較やINDEXとの組合せについてわかりやすく解説
ROW関数についてROWの概要行番号を取得するExcel関数=ROW( 対象 )概要 そのセルの行番号を取得する =ROW()と、引数をナシとすると、その関数の位置の行番号が表示される 偶数であるかを判断するISEVEN、奇数であるかを判断するISODD関数と組み合わせて使用されることが多い 行番号(1,2,3,4,5...
programming-cafe.com
VALUE関数で文字列を数値に変換する方法とエラーや空白の対処についてわかりやすく解説
VALUE関数についてVALUEの概要文字列をシリアル値などの数値に変換Excel関数=VALUE( 文字列 )概要 文字列をシリアル値などの数値に変換する 文字列*1 をして数値化することもできる 数値を文字列化する関数はTEXT 日付のほかには、時刻(14:00など)、通貨、％、分数などがあるVALUEはどのような...
programming-cafe.com