この記事は更新して新しいブログに引っ越しました。
↓↓↓
関数で市区町村を抽出！LEFT・IFERROR・FIND｜Excel｜PCワークLABO
住所録から「区」「市」「郡」まで抜き出した表
【住所】の列から、”区”がある場合は”区”まで、”区”がない場合は”市”まで、それもない場合は
”郡”までを【住所1】の列に抜き出して表示したい。
ちなみに【住所2】は【住所1】以降の部分を抜き出している。
文字列の左から指定した数を抜き出す「LEFT関数」を使いたいけれど、住所によって何文字目まで抜き出すのかは違ってくる。
そこで、「FIND関数」と組み合わせてみる。
”区”を見つけたらそこまでを抜き出し、なければ”市”まで、それもなければ”郡”まで…と。
ただし、「FIND関数」は文字が見つからない場合、”#VALUE!”などのエラーで返ってくる。
”エラーの場合は…”としたいので「IFERROR関数」とも組み合わせよう。
【B2】の関数はこうなる。
=IFERROR(LEFT(A2,FIND(“区”,A2)),IFERROR(LEFT(A2,FIND(“市”,A2)),LEFT(A2,FIND(“郡”,A2))))
この関数を文章で書くと以下のようになる。
【A2】で”区”の文字を見つけて左からそこまで抜き出す。
エラー（”区”がない）の場合は”市”を見つけてそこまで抜き出す。
まだエラー（”市”もない）の場合は”郡”を見つけてそこまで抜き出す。
では関数を一つずつ確認していこう。
LEFT関数
文字列の左端から指定した数だけ文字を抜き出す関数
例：【A1】のセルの左から２文字抜き出したい。
=LEFT(A1,2)
この結果は”あい”となる。
今回のセルではどうだろう。
=LEFT(A2,？)
【A2】のセルをLEFT関数で抜き出すのはわかる。
でも何文字抜き出せばいい？
そこでFIND関数が必要になってくる。
FIND関数
セル（文字列）から指定した文字を探して何文字目にあるのか求める関数。
例：【A1】のセルから”う”の文字を探して何文字目にあるのか求める。
（ここでは先頭の１文字目から探したいので「開始位置」は省略する。）
=FIND(“う”,A1)
この結果は”3”となる。
今回のセルではどうだろう。
=LEFT(A2,？)
先ほどの文字数？の部分にFIND関数を追加する。
=LEFT(A2,FIND(“区”,A2))
【A2】のセルから”区”を見つけLEFT関数でそこまでの文字数を抜き出す。
FIND関数では検索した文字が見つからない場合、エラーで返ってくる。
つまり、”区”が見つからなかった場合エラーとなってしまう。
次はIFERROR関数が必要になる。
IFERROR関数
エラーが表示される計算結果となる場合、代わりの値または計算結果を表示させる関数。
例：【A2】のセルから”区”の文字が何文字目にあるのかFIND関数で求めたいが、エラーになる場合は”なし”と表示させたい。
=IFERROR(FIND(“区”,A2),”なし”)
【B2】の結果は”なし”となる。（【B3】の結果は”5”となる）
今回の表ではどうだろう。
LEFT関数までで出来た =LEFT(A2,FIND(“区”,A2)) にIFERROR関数を追加する。
=IFERROR(LEFT(A2,FIND(“区”,A2)),LEFT(A2,FIND(“市”,A2))）
【A2】のセルから”区”を見つけLEFT関数でそこまでの文字数を抜き出す。
もしエラーになる場合は”市”を見つけLEFT関数でそこまでの文字数を抜き出す。
しかし、今回はこれだけでは終わらない。
【A2】は”区”も”市”もないので結局エラーとなってしまう。
そこで更に「エラーになる場合は”郡”まで抜き出す」を追加しよう。
「エラーの場合の値」にIFERROR関数をもう一つ入れる。
=IFERROR(LEFT(A2,FIND(“区”,A2)),IFERROR(LEFT(A2,FIND(“市”,A2)),LEFT(A2,FIND(“郡”,A2))))
これをコピーすれば全てエラーがなく表示されてくるようになる。
都道府県を抜き出すFINDとLEFT？それともMID？／Excel…の記事はこちらから
住所録から「住所1」以降の文字列を抜き出す
【住所2】に、【住所1】以降の文字列を【住所】から抜き出して表示させたい。
文字列の右から指定した数を抜き出す「RIGHT関数」を使いたいけれど、住所によって何文字目まで抜き出すのかは違ってくる。
そこで、「LEN関数」と組み合わせてみる。
【住所】の文字列の右側から【住所1】の文字数分引いたものを表示…と。
【C2】の関数はこうなる。
=RIGHT(A2,LEN(A2)-LEN(B2))
この関数を文章で書くと以下のようになる。
【A2】の文字数から【B2】の文字数を引いた数分【A2】の文字列の右側から抜き出す。
では関数を一つずつ確認していこう。
RIGHT関数
文字列の右端から指定した数だけ文字を抜き出す関数
例：【A1】のセルの右から3文字抜き出したい。
=RIGHT(A1,3)
この結果は”うえお”となる。
今回のセルではどうだろう。
=RIGHT(A2,？)
【A2】のセルをRIGHT関数で抜き出すのはわかる。
でも何文字抜き出せばいい？
そこでLEN関数の登場。
LEN関数
文字列の長さ（文字数）を求める関数
長さを意味するlengthからきている。
例：【A1】のセルの文字列が何文字か知りたい。
=LEN(A1)
この結果は”5”となる。
今回の表で、先ほどのRIGHT関数と組み合わせてみよう。
=RIGHT(A2,？)
先ほどの文字数？の部分にLEN関数を追加する。
=RIGHT(A2,LEN(A2)-LEN(B2))
【A2】の文字数から【B2】の文字数を引いた数分【A2】の文字列の右側から抜き出す。
これで【住所1】も【住所2】もきれいに埋まった。
