IFERROR・FIND関数で市区郡を抜き出そう/Excel

エクセルアイキャッチIFERROR Excel

住所録から「区」「市」「郡」まで抜き出した表

【住所】の列から、”区”がある場合は”区”まで、”区”がない場合は”市”まで、それもない場合は
”郡”までを【住所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関数

文字列の左端から指定した数だけ文字を抜き出す関数

LEFT関数
=LEFT(文字列,文字数)
文字列:文字を抜き出したいセルを指定
文字数:左から何文字目まで抜き出したいか数を指定

例:【A1】のセルの左から2文字分抜き出したい。
=LEFT(A1,2)
この結果は”あい”となる。

LEFT関数例

今回のセルではどうだろう。

市区郡を抜き出した表

=LEFT(A2,)
【A2】のセルをLEFT関数で抜き出すのはわかる。
でも何文字抜き出せばいい?

そこでFIND関数が必要になってくる。

FIND関数

指定した文字をセル(指定した文字列)から見つけて、何文字目にあるのか求める関数。

FIND関数
=FIND(検索文字列,対象,[開始位置])
検索文字列:探したい文字を””で括って指定(大文字と小文字は区別する)
対象:探したい文字を含むセルを指定
開始位置:「対象」セルの何文字目から検索するのかを指定(省略した場合は先頭の1文字目から検索開始

例:【A1】のセルから”う”の文字を探して何文字目にあるのか求める。
(ここでは先頭の1文字目から探したいので「開始位置」は省略する。)
=FIND(“う”,A1)
この結果は”3”となる。

FIND関数例

今回のセルではどうだろう。

市区郡を抜き出した表

=LEFT(A2,)
先ほどの文字数?の部分にFIND関数を追加する。

=LEFT(A2,FIND(“区”,A2))
【A2】のセルから”区”を見つけLEFT関数でそこまでの文字数を抜き出す。

FIND関数では検索した文字が見つからない場合、エラーで返ってくる。
つまり、”区”が見つからなかった場合エラーとなってしまう。
次はIFERROR関数が必要になる。

IFERROR関数

エラーが表示される計算結果となる場合、指定した値または計算結果を表示させる関数。

IFERROR関数
=IFERROR(値,エラーの場合の値)
:計算式(値とはその計算式の結果のこと)
エラーの場合の値:「値」がエラーとなる場合、エラーコードの代わりに表示させたい値もしくは計算式 ※単にエラーコードを表示させたくない場合は””(ダブルクォーテーション2つ連続入力)で非表示とさせる。

例:【A2】のセルから”区”の文字が何文字目にあるのかFIND関数で求めたいが、エラーになる場合は”なし”と表示させたい。
=IFERROR(FIND(“区”,A2),”なし”)
【B2】の結果は”なし”となる。(【B3】の結果は”5”となる)

IFERROR関数の例

今回の表ではどうだろう。

市区郡を抜き出した表

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))))

これをコピーすれば全てエラーがなく表示されてくるようになる。

住所録から「住所1」以降の文字列を抜き出す

【住所2】に、【住所1】以降の文字列を【住所】から抜き出して表示させたい。

住所録完成例

文字列の右から指定した数を抜き出す「RIGHT関数」を使いたいけれど、住所によって何文字目まで抜き出すのかは違ってくる。

そこで、「LEN関数」と組み合わせてみる。
【住所】の文字列の右側から【住所1】の文字数分引いたものを表示…と。

【C2】の関数はこうなる。

=RIGHT(A2,LEN(A2)-LEN(B2))
この関数を文章で書くと以下のようになる。
【A2】の文字数から【B2】の文字数を引いた数分【A2】の文字列の右側から抜き出す。

では関数を一つずつ確認していこう。

RIGHT関数

文字列の右端から指定した数だけ文字を抜き出す関数

RIGHT関数
=RIGHT(文字列,文字数)
文字列:文字を抜き出したいセルを指定
文字数:右から何文字目まで抜き出したいか数を指定

例:【A1】のセルの右から3文字分抜き出したい。
=RIGHT(A1,3)
この結果は”うえお”となる。

RIGHT関数例

今回のセルではどうだろう。

住所録完成例

=RIGHT(A2,)
【A2】のセルをRIGHT関数で抜き出すのはわかる。
でも何文字抜き出せばいい?

そこでLEN関数の登場。

LEN関数

文字列の長さ(文字数)を求める関数
長さを意味するlengthからきている。

LEN関数
=LEN(文字列)
文字列:文字数を数えたいセルを指定

例:【A1】のセルの文字列が何文字か知りたい。
=LEN(A1)
この結果は”5”となる。

LEFT関数例

今回の表で、先ほどのRIGHT関数と組み合わせてみよう。

住所録完成例

=RIGHT(A2,)

先ほどの文字数?の部分にLEN関数を追加する。

=RIGHT(A2,LEN(A2)-LEN(B2))
【A2】の文字数から【B2】の文字数を引いた数分【A2】の文字列の右側から抜き出す。

これで【住所1】も【住所2】もきれいに埋まった。

タイトルとURLをコピーしました