[Googleスプレッドシート] 数字以外が入っているセルだけ表示する
Office※ 本記事はアフィリエイト広告(Amazonアソシエイト)を含みます。
電話番号や郵便番号、社員番号の列に、なぜか文字が混ざっている。そういう「数字だけのはずなのに、数字以外が入っているセル」を洗い出したいときのメモです。
これも正規表現で。
基本形
=IF(REGEXMATCH(TO_TEXT(A2),"\D"),A2,"")
\D は「数字以外の1文字」。REGEXMATCH はそれが1文字でも含まれていれば TRUE を返すので、
| A2 | REGEXMATCH | 結果 |
|---|---|---|
12345 | FALSE | (空白) |
123-45 | TRUE | 123-45 |
abc | TRUE | abc |
と、数字以外が混ざっているセルだけが表示されます。
⚠️ 以前この記事では「数字以外の文字列が入っている場合は false が返る」と書いていましたが、逆でした。数字以外を含む → TRUE → A2 を表示、が正しい動きです。
TO_TEXT を挟む理由
REGEXMATCH は文字列しか受け付けないので、数値が入っているセルをそのまま渡すとエラーになります。TO_TEXT(A2) で文字列にしてから渡せば、数値のセルでも普通に判定できます。
逆に、数字だけのセルを表示する
出力を逆にすればいい。
=IF(REGEXMATCH(TO_TEXT(A2),"\D"),"",A2)
「全部が数字かどうか」をはっきり書きたいなら、こちらの方が読みやすいかもしれません。
=IF(REGEXMATCH(TO_TEXT(A2),"^\d+$"),A2,"")
数字の部分だけ取り出す
判定ではなく、TEL: 03-1234 から数字を取り出したいなら REGEXEXTRACT。
=REGEXEXTRACT(TO_TEXT(A2),"\d+")
ただしこれは最初に出てくる数字のかたまりだけを返します(上の例なら 03)。数字以外を全部消したいなら REGEXREPLACE の方。
=REGEXREPLACE(TO_TEXT(A2),"\D","")
気をつけたい文字
| 入力 | 判定 | 理由 |
|---|---|---|
123(全角数字) | 数字以外あり | \d は半角の 0〜9 だけ |
1.5 / -3 | 数字以外あり | . や - も数字以外 |
123 (末尾スペース) | 数字以外あり | 見えないスペースも1文字 |
逆に言えば、全角数字や紛れ込んだスペースを見つけるのにも使えます。小数やマイナスを数字として扱いたいなら、ISNUMBER(A2) で「数値かどうか」を判定する方が向いています。
該当セルだけを一覧にする
1行ずつ判定する代わりに、FILTER で数字以外を含むセルだけを抜き出せます。
=FILTER(A2:A,A2:A<>"",REGEXMATCH(TO_TEXT(A2:A),"\D"))
まとめ
| やりたいこと | 式 |
|---|---|
| 数字以外を含むセルを表示 | =IF(REGEXMATCH(TO_TEXT(A2),"\D"),A2,"") |
| 数字だけのセルを表示 | =IF(REGEXMATCH(TO_TEXT(A2),"^\d+$"),A2,"") |
| 数字以外を消す | =REGEXREPLACE(TO_TEXT(A2),"\D","") |
| 該当セルを一覧に | =FILTER(A2:A,A2:A<>"",REGEXMATCH(TO_TEXT(A2:A),"\D")) |
数値セルが混ざるなら TO_TEXT を挟む。これを忘れるとエラーになりがちです。
GoogleSpreadsheet