住所から特定文字列を抽出するMID/FIND/RIGHT/LEFT/LEN/SUBSTITUTE/IFERROR関数の組み合わせ-GSS/Excel/関数

文字列から文字を抽出するMID/FIND/RIGHT/LEFT/LEN/SUBSTITUTE/IFERROR関数の組み合わせについて説明していきます。

今回は住所のデータからいろいろな抽出方法を見ていきます。

すでに基本的な使い方を文字列からと特定の値を抽出したいMID関数RIGHT関数LEFT関数-GSS/Excel/関数で説明しましたが、今回はさらに応用編を説明していきます。

例題

下図のような住所データですが、データベースを作るにあたっては本当はそれぞれ別のカラム（列）にして、フィルタリングしやすいようにするのが一番いいです。ただどこか基幹システムを使っていて、そこからCSVで落とした時に、いい感じのデータになっておらず、下図のように１セルにすべて入れ込んでいる厄介なものが多いです。

こうしたデータからフィルタリング・分析しやすくするために、文字列抽出をしていきます。

さっそく解答例を見ていきましょう。

解答

今回は上図のようにわけてみました。市区町村の扱いどうする…？みたいなところがあるので、今回は、市区町村はそれぞれ別のカラムにデータが入るようにしました。

またJ列に「残」というものがありますが、元データを抽出元にして後半の文字列抽出するのが面倒だったので、演算用に作ったものです。演算用シートではこういうカラムを作ってそれぞれのセルに入れる関数をなるべく短くすると、関数を確認するときに楽です。なるべく関数は長々書かないほうがいいとは思います。

難しいところだけ説明していきます。

解答例

[B2]=LEFT(A2,8) ⇒ 108-0000

[C2]=LEFT(A2,FIND("-",A2)-1) ⇒ 108

[D2]=MID(A2,FIND("-",A2)+1,FIND(" ",A2)-(LEN(C2)+1)) ⇒ 0000

[E2]=if(countif(A2,"*京都府*")=1,"京都府",IFERROR(IFERROR(IFERROR(MID(A2,FIND(" ",A2)+1,FIND("都",A2)-FIND(" ",A2)),(MID(A2,FIND(" ",A2)+1,FIND("府",A2)-FIND(" ",A2)))),(MID(A2,FIND(" ",A2)+1,FIND("道",A2)-FIND(" ",A2)))),(MID(A2,FIND(" ",A2)+1,FIND("県",A2)-FIND(" ",A2))))) ⇒ 東京都

[F2]=SUBSTITUTE(IFERROR(MID($A2,FIND(" ",$A2)+1,FIND(F$1,$A2)-FIND(" ",$A2))),$E2,"") ⇒ ""

[G2]=SUBSTITUTE(IFERROR(MID($A2,FIND(" ",$A2)+1,FIND(G$1,$A2)-FIND(" ",$A2))),$E2,"") ⇒ 港区

[H2][I2]は[F2][G2]と同じ関数（コピペで対応できる）のため割愛。

[J2]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,C2,""),D2,""),E2,""),F2,""),G2,""),H2,""),I2,"") ⇒ -六本木1-12-123 六本木ビル1F

[K2]=IFERROR(MID(J2,FIND("-",J2)+1,FIND(" ",J2)-1),SUBSTITUTE(J2,"-","")) =六本木1-12-123

[L2]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,""),"-","")," ","") ⇒ 六本木

[M2]=SUBSTITUTE(K2,L2,"") ⇒ 1-12-123

[N2]=IFERROR(RIGHT(J2,LEN(J2)-FIND(" ",J2)),"") ⇒ 六本木ビル1F

解答まとめ

まとめると下記のとおりです。

元データ

郵便番号

郵便番号上

郵便番号下

都道府県

市

区

町

村

残

番地

種別

番号

その他

抽出結果

108-0000 東京都港区六本木1-12-123 六本木ビル1F

108-0000

108

0000

東京都

港区

-六本木1-12-123 六本木ビル1F

六本木1-12-123

六本木

1-12-123

六本木ビル1F

関数

LEFT(A2,8)

LEFT(A2,FIND("-",A2)-1)

MID(A2,FIND("-",A2)+1,FIND(" ",A2)-(LEN(C2)+1))

if(countif(A2,"*京都府*")=1,"京都府",IFERROR(IFERROR(IFERROR(MID(A2,FIND(" ",A2)+1,FIND("都",A2)-FIND(" ",A2)),(MID(A2,FIND(" ",A2)+1,FIND("府",A2)-FIND(" ",A2)))),(MID(A2,FIND(" ",A2)+1,FIND("道",A2)-FIND(" ",A2)))),(MID(A2,FIND(" ",A2)+1,FIND("県",A2)-FIND(" ",A2)))))

SUBSTITUTE(IFERROR(MID($A2,FIND(" ",$A2)+1,FIND(F,$A2)-FIND(" ",$A2))),$E2,"")

SUBSTITUTE(IFERROR(MID($A2,FIND(" ",$A2)+1,FIND(G,$A2)-FIND(" ",$A2))),$E2,"")

SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(IFERROR(MID($A2,FIND(" ",$A2)+1,FIND(H,$A2)-FIND(" ",$A2))),$E2,""),$F2,""),$G2,"")

SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(IFERROR(MID($A2,FIND(" ",$A2)+1,FIND(I,$A2)-FIND(" ",$A2))),$E2,""),$F2,""),$G2,""),$H2,"")

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,C2,""),D2,""),E2,""),F2,""),G2,""),H2,""),I2,"")

IFERROR(MID(J2,FIND("-",J2)+1,FIND(" ",J2)-1),SUBSTITUTE(J2,"-",""))

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,""),"-","")," ","")

SUBSTITUTE(K2,L2,"")

IFERROR(RIGHT(J2,LEN(J2)-FIND(" ",J2)),"")

詳細説明

LEFTやRIGHT単体で出るものは文字列からと特定の値を抽出したいMID関数RIGHT関数LEFT関数-GSS/Excel/関数で理解できると思うので割愛します。

都道府県名を抽出する

都道府県名を抽出する [E2]=if(countif(A2,"*京都府*")=1,"京都府",IFERROR(IFERROR(IFERROR(MID(A2,FIND(" ",A2)+1,FIND("都",A2)-FIND(" ",A2)),(MID(A2,FIND(" ",A2)+1,FIND("府",A2)-FIND(" ",A2)))),(MID(A2,FIND(" ",A2)+1,FIND("道",A2)-FIND(" ",A2)))),(MID(A2,FIND(" ",A2)+1,FIND("県",A2)-FIND(" ",A2))))) を説明します。

一つ一つは難しくありません。

= (MID(A2,FIND(" ",A2)+1,FIND("都",A2)-FIND(" ",A2))

上記の式が、この関数の肝になっています。「空白」から「都」の文字まで抽出するという関数です。

このままでは他の道府県がエラーになってしまいます。そこで、エラーを利用して、「IFERROR関数」を使います。「都」がみつからずエラーだった場合、「空白」から「府」までを抽出する。さらにエラーだったら「空白」から「道」までを抽出する。さらにエラーだったら「空白」から「県」まで抽出する。となります。

基本これでOKなんですが、「京都府」がデータに入っている場合、「都」が邪魔してしまうので、一番最初に、「京都府」が含まれる文字列の数を調べて、1つあれば「京都府」になるようにしています。

この関数一つをコピペすれば、どんな住所でも都道府県名が抽出可能です。

市区町村名を抽出する

次に [G2]=SUBSTITUTE(IFERROR(MID($A2,FIND(" ",$A2)+1,FIND(G$1,$A2)-FIND(" ",$A2))),$E2,"") を説明していきます。

SUBSTITUTE関数は、その名の通り置換する関数です。

基本形は、下記の通り。

=SUBSTITUTE(対象セル,置換したい文字,置換後の文字)

この数式の基本も難しくありません。

IFERROR(MID($A2,FIND(" ",$A2)+1,FIND(G$1,$A2)-FIND(" ",$A2))) が基本です。

このまま関数を実行すると、都道府県名まで含まれている状態で抽出されてしまいます。

そこで、SUBSTITUTE関数を使って、都道府県名がなかったことにします。それで出来上がるのが下記の関数になります。

[G2]=SUBSTITUTE(IFERROR(MID($A2,FIND(" ",$A2)+1,FIND(G$1,$A2)-FIND(" ",$A2))),$E2,"")

この構造を応用できればほかのセルもすべて出すことができます。

まとめ

• SUBSTITUTE関数の使い所
  • 邪魔な文字列を削除したり置換することでデータ抽出をしやすくする
• IFERROR関数の使いどころ
  • 作った数式の抜け（エラー）に対して処理を行う
• 構文
  • =MID(対象セル,抽出開始位置,抽出したい文字数)
  • =RIGHT(対象セル,文字数)
  • =LEFT(対象セル,文字数)
  • =SUBSTITUTE(対象セル,置換したい文字列,置換後の文字列)
  • =IFERROR(エラー対象,エラー時の処理)

おまけ

実業務の際に、上記のように格納されているデータがある程度掌握できる範囲であれば、関数のみで対応することが多いですが、どんなデータが入っているか掌握しきれない場合は、どこかのWEBページから市区町村のデータをスクリーニングして対照表を作って、それを利用するとよいです。

たとえば四日市市とか市川市とかがデータの中に入ってると意図しない抽出結果になります。

今あるデータだけで解決しようとするのではなく、追加でデータベースを作ることで、作業が楽になることも多いということを頭にいれておくとよいと思います。