はじめに
住所など数字を含む文字列の最初に出てくる数字の左から数えた位置を返す数式、
またそれを利用して文字列から最初の数字の前までの文字列を抜き出す数式について
図を用いて簡単に解説をします。
さらに応用すれば特定の記号までの文字列を抜き出すことも可能です！
文字列の最初に出てくる数字の位置を返す数式
まずは数字の位置を返す数式から見てみましょう！
<例で使用している数式>
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789"))
すごい、ちゃんと数字の位置が返されていますね！
『&”0123456789″』のところから工夫している感が溢れ出てます！
おおっ、鋭いですね！
FIND関数は文字列に検索値が無いとエラーになります。
そして、エラーを含む配列をMIN関数に渡すとこれまたエラーになっちゃいます。
探すようお願いされたのに無いなんてあんまりです…#VALUE!
FINDちゃん…
あ、それでFIND関数がかならずヒットできるよう
末尾に『&”0123456789″』をくっつけているんですね！
そのとおりです！
FIND関数は一番最初に検索値が出てくる位置を返すので、
対象文字列が『KSC00123』のとき
文字列『KSC001230123456789』で
0～9が最初に出てくる位置を返します。
そうやってゲットした配列の中から
MIN関数で一番小さい数字を取り出せば、
最初に出てくる数字の位置、ということになります！
配列数式、やっぱり難しいです…！
この数式ですと対象文字列に数字が含まれない場合、
対象文字列の文字数+1が返ります。
『&”0123456789″』の部分が0でヒットした位置が一番小さい数字になって、
MIN関数が返すからです。
なんだか、ちょっとスッキリしませんね。
数字は無いのに…
ですよね。
ちょっと長い式になりますが対策した数式を考えました。
対象文字列に数字が無い時に0を返す数式
次の見出しでもっと短い式を紹介しますので、数式を利用する際はそちらも読んで判断して頂くと良いと思います。
<例で使用している数式>
=IF(AND(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},B4))),0,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789")))うわぁ、長いですね。
IF関数の条件分岐で、対象文字列に数字を含まない場合は
0を返すようにしています。
そうでない場合は先程の数式でそのまま数字の位置を返します。
AND関数…セル範囲同士の比較で使ったのと同じ方法ですね？
そうです！
文字列から0～9を探して、FIND関数の結果が全てエラーならISERROR関数の結果が全てTRUEになって、
AND関数はTRUEを返し、IF関数の分岐で0を返します。
0～9の数字を一つでも含んでいるとAND関数はFALSEを返します。
頭がこんがらがってきました…
数式をコピーしてみて、内側から紐解いていくと分かりやすいですよ。
より短い数式
より短い数式をTwitterで光希桃先生から教わったので、ご紹介です
<例で使用している数式>
=MOD(MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&1234567890)),LEN(B4)+1)これは先程に比べずいぶん短くなりましたね！！
0～9の配列が1つになって、とても綺麗ですよね！！
MOD関数…
これはどういう仕組で数字が無い時に
0を返しているんでしょうか
そこが気になりますよね！
では、対象文字列に数字が一切ない場合を想定して順に考えてみましょう。
MIN関数はFIND関数が対象文字列と『1234567890』を結合した文字列から1を探した結果、『文字列+1』を返します。
そしてMOD関数は『文字列+1』を『文字列+1』で割った余り0を返します。
対象文字列に数字がある場合のMOD関数はどうなりますか？
画像の1番上の例を見てみましょう。
対象文字列の10文字目に『3』が出てきているのが最初の数字ですね。
MIN関数はFIND関数が返す数値の中で一番小さい数字『10』を返します。
そして、MOD関数は『10』を『文字列+1』で割った余り10を返します。
はぁ～っ、美しい数式ですね…
いつかこんな数式が考え出せるようになりたいものです！
文字列の最初に出てくる数字より前の文字列を返す数式
<例で使用している数式>
=LEFT(B4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789"))-1)先程までの数字の位置を返す数式を応用して。
数字の前の文字列だけを抜き出す数式を紹介します
住所文字列から番地を取り除く時に活躍するんですね！
LEFT関数に『左から何番目に数字があるか』という値から1を引いた数値を引数として渡すのがポイントです！
なるほど、それで数字の前までを返すわけですね
対象文字列に数字がない時はそのまま全文を返します
最初に紹介した数式は、このために対象文字列の文字数を返していたんですね
フラッシュフィルを使う方法
これは関数を使う方法ではないのですが
フラッシュフィルを使う方法があります
ショートカットキーは『Ctrl + E』ですね！
この方法はちょっと問題があって、数式での方法と違って
結果が狙い通りにならない可能性がありますので注意が必要です
動画の例だと、フラッシュフィル後に番地が残るケースもあるようでしたね
そうなんです
便利なのですが、中身が見えないのが欠点です
数式だと難解に感じるかもしれませんが、
実現しようとしていることは解読可能です
おわりに
Twitterで教えて頂いた数式について自分なりに解説をしてみました。
住所文字列の番地の手前までの文字列を抽出するというのは需要ありそうな気がしました。
応用すると特定の記号が出てくる位置、また手前の文字列を抜き出すことも可能です。
色々変えて使ってみて下さい！
【外部リンク】FIND関数、FINDB関数|Microsoftサポート
最後までお付き合い頂き、ありがとうございました！
当ブログはエクセル関数の擬人化キャラクター記事をメインコンテンツとしています！
よろしければこちらの記事もどうぞ～！
コメント
[…] 住所など数字を含む対象文字列に数字が出てくる位置が左から何文字目なのかを返す数式【エクセルTips】住所など数字を含む文字列の最初に出てくる数字の左から数えた位置を返す数式 […]