Googleスプレッドシート 検索と置換を使いこなそう！（正規表現で限界突破）
Googleスプレッドシートの 機能 「検索と置換」 について 掘り下げてみましょう。
基本的な使い方はもちろん、Excelの検索と置換 機能との違いや、Excelでは出来る処理を Googleスプレドシートではどう代替できるか？
そして正規表現を使った 検索と置換の 応用、さらに GASにおける 検索と置換 Textfinder を使って出来ることなど。
他のサイトとは一味違う活用テクニックや ディープな使い方 まで書いていきたいと思います。（当然、1回では終わりませんｗ）
先週までは SORT関数シリーズを4回に渡って掲載。前回はマニアックな SORTN関数の超応用例について書きました。
Googleスプレッドシート「検索と置換」の基本
まずは Googleスプレッドシートの「検索と置換」の基本から。
知ってましたか？ Googleスプレッドシートの「検索と置換」は 3種類あります。
Ctrl + F で起動する 簡易検索
Ctrl + H で起動する 検索と置換 （メニュー ＞ 編集からも開ける）
スマホアプリ版の 検索と置換
まずは、各々がどのような違いがあるか見てみましょう。
1. Ctrl + F で起動する 簡易検索
Windows PCの場合は ショートカット Ctrl + F（Macだと command + F）で右上に ピョコンと小窓が出てくるのが 簡易検索です。
メニューからマウス選択では開く方法のない、ショートカットでのみ使える変わった機能です。
小窓に検索したいワードを入れれば、その文字列を含むセルを検索していきます。
Chromeのページ内検索と似てますね。ただあくまでも簡易検索なので、細かい設定は出来ませんし、置換機能もありません。
そして、検索範囲は 開いているシートのみ です。ブック全体を検索することは出来ません。
しかし次に紹介する「検索と置換」機能に比べて良い点もあり
検索でヒットしたセル が緑色で強調表示され目立つ
ヒットしたセルが全何件で、何件目を選択しているか一目でわかる
これらは、検索と置換には無い 機能です。
ここから、置換機能を使いたい、または 検索範囲をブック全体に広げたい、オプションで細かい検索条件を設定したいといった場合は、
「その他オプション」 （検索窓の 右の ︙ ）
から 「検索と置換」に移行することも出来ます。
さくっと検索したい時には便利な機能です。
2. Ctrl + H で起動する 検索と置換 （メニュー ＞ 編集からも開ける）
他のシートも含めたブック全体の検索をしたり、置換処理をしたり、オプション設定等でフル機能を使った検索したい といった時に使うのが 、今回の記事のメインとなる「検索と置換」です。
簡易検索からも開けますが、直接開くなら ショートカット Ctrl +H、またはメニューから 編集 ＞ 検索と置換 でも開けます。
簡易検索に比べれば多機能なんですが、ヒットしたセルに色がつかない（強調表示なし）はともかく、検索でヒットしたセル件数が出ないのが結構不満だったりします。
詳しくは後述。
3. スマホアプリ版の 検索と置換
なにかと制限の多いスマホアプリ版のスプレッドシートですが、やはり検索と置換もフル機能は使えません。
ざっくり言うと Android版、iOS版 どちらも 簡易版 検索と置換といったところです。
出来ることは 開いているシートを対象とした 検索キーワードを含む セルの検索、そして置換（一括置換）です。一応、件数表示もあります。
残念ながらアプリ版では ブック全体（複数シート）の検索、置換が出来ません。これが残念。
iPadスプレッドシートのセル内改行の記事でも触れましたが、Googleスプレッドシートは、アプリ版で出来なくてもスマホのブラウザをPC版表示にすることで、ちょっと操作はしにくいですが PCで開いたのと同等の処理ができます。
どうしてもスマホから 本格的な検索と置換をしたい場合は、スマホブラウザで スプレッドシートを開き 検索と置換を使いましょう。
Googleスプレッドシート「検索と置換」基本の使い方
3つの検索と置換の2番目、メインとなる 「検索と置換」機能の基本的な使い方を確認していきましょう。
「検索」
・（基本は）指定した文字列を 含むセルを 検索できる
・ 検索範囲は、ブック（全シート）、開いているシート、特定の範囲から指定できる
・ 検索は 今開いているシートのアクティブなセルから、シート内を 横方向に 左から右にスキャンして下に降りる動き
・ ブック（全シート）検索の場合は、アクティブシートの検索が終わると次に 一つ右のシートを見に行く。一周すると 今のシートに戻ってくる。
・（基本は）数式の結果、表示形式で出力された セルに表示されている 値を検索対象とする
・ 検索ボタンを押すたびに次に検索にヒットしたセルがアクティブになる
基本となる検索は、このようになっています。残念ながら検索結果の一覧表示、件数表示はありません。
続いて 検索オプションを見ていきましょう。
「検索オプション」
・ 大文字と小文字の区別
アルファベットの大文字と小文字を区別する
・完全に一致するセルを検索
検索ワードを含むではなく一致するセルを検索する
・正規表現を使用した検索
正規表現を使うことが出来る （後述）
・数式内も検索
数式内の文字も検索できる
・リンク内も検索する
ハイパーリンクのリンク内も検索する
上の2つはいいですね。正規表現については 後半で詳しく説明します。
数式内も検索 にチェックを入れると、通常は セルに表示された結果を検索するモードが変わり、数式の中を検索対象とします。逆に 数式の結果は検索対象からは除外となります。
また、カスタム数値形式で "0個" と設定した 10個と表示されているセルも、通常は個 で検索してヒットしますが、数式内も検索とした場合は 数式バーに表示されない 個は 検索対象外となります。
リンク内も検索する も、その名前の通りで チェックをつけると ハイパーリンクのURLを含めて検索ができます。
コメントやメモ、図形描写内のテキストを検索するオプションはありません。
「置換」「すべて置換」
・ 置換は 検索を実行した後に使える
・ 検索ワードを指定した文字にセル単位で1件ずつ置換していく
・ 1つのセルに 複数個検索ワードがあった場合も全て置換する
・数式の結果や 表示形式で付与された文字は 検索は出来ても置換はできない
・ すべて置換を実行すると 一括で 検索範囲でヒットした ものを
全部置換する
・すべて置換した場合、置換した件数（セル数ではない）が表示される
当然ですが、数式の結果は 置換できません。逆に言えば数式内にチェックを入れない検索と置換であれば、数式が値化したり壊れたりすることはないってことです。
置換はセル単位になるので、セル内に検索ワードと一致する文字列が複数あった場合は、そのセルの中は一気に置換されます。
「すべて置換」を使うと 一気に該当する箇所が全て置換されます。
ここで表示される件数は、セル数ではなくヒットした文字列の数となります。
他にもっと丁寧に説明しているサイトもありますんで、基本はこんなもんでいいでしょう。
Excel の検索と置換との違い
検索と置換は、表計算アプリの代表である Excelにもある機能です。
では、Googleスプレッドシートの 検索と置換は、Excel の検索と置換と比べて なにが出来てなにが出来ない のか？
次はこの視点で見ていきましょう。
Excelの検索と置換は 詳しく紹介しているサイトがいっぱいあるので、これも詳しい解説は割愛します。さらっと出来ることだけ書いていきます。
つい先日も 窓の杜で Excelの検索と置換は記事になってました。
Web版（無料）の Excelの検索と置換は 必要最低限の機能
mirは 365ユーザーではないので、検証に使えるのは 無料のWeb版と買い切りインストール版の Excel2019 となります。
とりあえず Web版Excelに関しては 検索と置換は 必要最低限の機能といった感じです。
面白いのは検索の方向を 上下切り替えできる点。インストール版だと 行方向、列方向って指定なんですが、なんでWebだと上下なんだろ？？どういう時に活用できるんでしょうか？
ワイルドカードも使えますし、ブック全体に対しての検索・置換も出来るし普通に使うには十分ですね。
インストール版 Excelの検索と置換は 圧倒的に多機能
無料のWeb版と違って、さすがに 有料のインストール版 Excelの 検索と置換は 圧倒的に多機能です。
その中には Googleスプレッドシートの 検索と置換では残念ながら出来ない、便利な（羨ましい）機能もあります。
書式を条件として検索や置換ができる
検索結果の一覧表示、件数表示ができる
半角と全角を区別せず検索ができる
改行に置換ができる
大きくはこの4つでしょうか。
他にもコメントを検索するとか、空白を検索といったことが Excelでは出来ますが、さほど影響が大きくはなかったり、正規表現など工夫でなんとか対応できるかなと思います。
Googleスプレッドシートでは出来ない 書式での検索・置換 と 結果の件数＆一覧表示
Excelでは セルの色や文字の色、太字といった書式設定で検索や置換ができます。これは凄いですよね。
これは Googleスプレッドシートだと GAS使わないと無理・・・。
色付きのセルが検索できて、一覧表示ができるということは、
このように、例えば 指定した色で検索した 結果のセルが幾つあるか、個数を確認できます。
さらに 検索結果の一覧表示 で Ctrl + Aで全選択することで、検索でヒットしたセルが アクティブになり、右下にシート内での その色のセルの数値合計（200） も表示させることができます。
Googleスプレッドシートでも 色を条件にして COUTIFしたい、SUMIFしたいって要件が結構ありますが、Excelだと コード書かなくても機能で対処することができるのは便利ですね！
ちなみに注意点として、Excelで検索できる書式は セル全体に設定されたものだけです。
このように、赤文字で検索した時に 「部分的に赤文字」になっている 高杉 恭子さんは 検索でヒットしません。
置換後の値でも書式を設定できます。
これは 条件付き書式で対応してもいいんですが、検索と置換で処理できることも知ってると便利かもしれません。
Googleスプレッドシートでは出来ない 半角・全角を区別しない検索
手入力のデータは 半角だったり全角だったりと 表記のゆれがあるんで、Googleスプレッドシートでは うまく検索できなくて困るケースが結構あります。
Excelの場合、検索の際に 半角と全角を区別するかどうかを設定できて、初期値は区別しないになってるんで、数字やアルファベット、カタカナを区別せず検索してくれます。
これが結構便利だったりします。
一方、Googleスプレッドシートの検索と置換は、厳密に 全角・半角を区別します。
ﾀﾅｶ で検索した場合、 タナカ はヒットしませんし
TANAKA では ＴＡＮＡＫＡ がヒットしません。
XLOOKUPの回で触れましたが、シート上でのイコールの一致はかなりざっくり（幅広い）なのがGoogleスプレッドシートなんですが、なぜか 検索と置換は 厳密に一致を判定してくれちゃいますｗ
Googleスプレッドシートには残念ながら半角全角を区別する・しないを切り替える設定がありません。これはGASを使っても厳しいです。
Googleスプレッドシートでは出来ない 改行への置換
Googleスプレッドシートでも 正規表現を使うことで 改行を検索することは出来ます。ただ、残念ながら 改行へ置換が出来ません。
一方、Excelは置換後の文字に 改行を設定できます。
上の画像は、東京都の後ろに改行を入れたいんで、東京都を 東京都+改行 に置換した結果です。
東京都の後ろに Ctrl +J で見えない改行を入れています。
これで 改行に置換することが出来るわけです。
Googleスプレッドシートでも、もちろん GASを使えば 改行への置換は できますし、裏技的にGAS無しで実現する方法もあるんですが、Excelが（知っていれば）普通に出来るのと比べると、不便だなと感じます。
あくまでも Googleスプレッドシートの「検索と置換」の記事なんで、Excelの方はサクッとこれくらいで終わりにしますが、やっぱ痒いところに手が届く機能が揃ってて便利だわーと思います。
Googleスプレッドシートの検索と置換は 正規表現が使える
Excelの検索と置換が多機能ってのは、よくわかったと思います。
しかし Googleスプレッドシートには、関数でもお馴染みの 正規表現があります。
検索と置換 機能において、Excelと比較してGoogleスプレッドシートの方が良いと言える、ほぼ唯一の点が この 正規表現が使えることです。
正規表現を使うことで、検索と置換で出来ることの幅がかなり広がります。
逆に言えば、
先頭や末尾に文字を挿入する
空白を - に置換する
改行を , に置換する
〇〇または △ △ といった OR検索をする
これらは 正規表現が使えないと 出来ない処理です。
Excelにも ワイルドカードがあるんで、ある程度はカバーできます。（逆にGoogleスプレッドシートは 検索と置換では ワイルドカードは使えません）
しかし、先頭や末尾を置換して 何か文字を足したり、2桁の数値のセルだけ検索対象としたり、ワイルドカードでヒットした文字列を置換後に使うといったことは出来ません。
超ざっくり言ってしまうと、ワイルドカードの超上位互換が 正規表現と言っても過言ではありません。それくらい便利です。
正規表現はプログラミングに触れたことのない人や不慣れな人には、なかなかハードルが高いものですが、今回は
検索と置換で使える「正規表現の基本」
を比較的丁寧に書きますんで、是非必要なところだけでも覚えて活用できるようにしましょう！
検索と置換 で使える正規表現の基本 1
正規表現はかなり奥が深いんで、検索と置換で使える本当に基本的な部分だけを 活用例と合わせて紹介します。
より詳しく知りたい方は "正規表現 基本" あたりで検索すると、優しい解説サイトが色々ありますので参考に。
お馴染み 「いきなり答える備忘録」さんも 正規表現をまとめています。
先頭や末尾に文字を差し込む
^ ・・・ 先頭
$ ・・・ 末尾
まずはこれです。
Googleスプレッドシートでは、検索と置換 を 正規表現モードで使うことで、選択した範囲に対して 先頭や末尾に一括で文字を入れることが出来ます。
たとえば
検索 ^
置換後の文字 血液型:
として、F列（血液型）の列を範囲選択した上で、 正規表現を使用した検索 にチェックを入れて 「すべて置換」すると
A,AB,O など血液型のアルファベットだけが入っていたセルが、血液型:A, 血液型AB, 血液型O といった具合に 先頭に セットした文字が付与されます。
同じく
検索 $
置換後の文字 歳
として、C列（年齢）の列を範囲選択した上で、 正規表現を使用した検索 にチェックを入れて 「すべて置換」すると
53,39,31 と年齢の数値のみだったセルが 53歳, 39歳, 31歳 と一括で 歳 が後ろについた状態になります。
もちろん、表示形式で対応した方がよい場合もあるでしょうが、このように検索と置換でも対応できることは 知っていた方がよいでしょう。
この正規表現による置換は、以下のような要件で活用できます。
選択した数字を一括で 2倍にしたい
隣のセルに =A2*2 と入れて下にフィルコピーすりゃ済む話ですが、どうしても他のセルを使えない場合、Excelだと 2を入れたセルをコピーして、演算貼付けで 乗算を選択することで セルの値を一括で2倍にすることができます。
残念ながら Googleスプレッドシートには、この 演算貼付けの機能はありません。
しかし、先ほどの先頭に文字を追加する 検索と置換を応用すると、以下のように処理できます。
検索 ^
置換後の文字 =2*
先頭に =2* を差し込むことで
中身は
200 → =2*200
見た目は
200 → 400
中身は数式化していますが、このように 2倍にする処理がセル内で実現できるわけです。
必要があれば、これをコピー値貼付けで値化しておきましょう。
正規表現で空白セルを置換する
^ ・・・ 先頭
$ ・・・ 末尾
^$ ・・・ 空白 （先頭と末尾の間になにもない）
先頭、末尾に続いて、この2つを組み合わせた 空白を覚えましょう。
これで空白セルに対する処理ができるようになりました！
しかし、これで空白埋めができるぞ～！と試しに実行してみるも
あれ！？となる人が結構います。
これも今は多くのサイトで紹介されているので、一度ググった人は知ってるネタですが、Googleスプレッドシートは 処理を軽くする為か、なにも操作を行っていないセルは 検索と置換の対象外となってしまう仕様なのです。
なんらかの操作をしたセルが 検索と置換の対象となります。
なんらかの操作とは、値または数式が入っている、フォントが設定されている、セルが塗りつぶしされている、枠線が引いてある などです。
影響の少ない フォント指定あたりを対象セルにしてから
検索と置換すれば
空白埋めが出来ました～。
知らないと ハマるポイントです。
パワークエリのような 上の文字（左の文字）で埋める が出来る
空白の置換を応用すると、このようなケースにも対応できます。
上の画像のように 各クラスの先頭にだけ クラス名（A, B, C, D）を入れていたものを 上の文字で空白を埋めたい。
Excelだったら パワークエリで一発処理ですが、Googleスプレッドシートだと作業列に 関数を入れて処理する方法が一般的です。
これを 1つの式で一気に処理する方法は、過去noteに書いてますので興味があれば参照ください。
では、作業列を使わずに 検索と置換でどう対処するのか？
検索 ^$
置換後の文字 =INDIRECT("R[-1]C",false)
空白をこのような式に置換することで実現できます。
ポイントは、検索と置換で入れる式は相対参照にはならないので、どのセルに入れても一つ上のセルを参照する汎用的な式を用意する必要がある点。
そんな式なんてあるのか？？と思う人もいるでしょうが、実は出来ます。
ここで使うのは INDIRECT を使った R1C1参照の 式を入れた自分のセルを起点とする参照の書き方です。
R1C1表記は、通常は
A1 ・・・ R1C1
A2 ・・・ R2C1
B1 ・・・ R1C2
B2 ・・・ R2C2
このように R1C1を A1として 記述する方式で Googleスプレッドシートでは INDIRECTの 第2引数を FALSE にすることでセル参照として機能します。
しかし、R1C1にはもう一つ式をいれた自分自身のセルを起点として その一つ上や一つ下を OFFSET参照する記述の仕方があります。
これを使うことで
どこに入れても 常に自分のセルの一つ上を参照する式を
=INDIRECT("R[-1]C",false)
このように記述することが出来ます。
この方法は、左の値で空白埋めや 参照したセルを加工して 出力するなど応用が色々できる点が魅力です。
さらに
ちょっと面倒な式を用意する必要はありますが、検索と置換の一括処理で 上のようにグループ毎の連番で空白を埋めるといった処理も可能です。（最初の A → A1 も検索と置換で可能）
検索 ^$
置換後の文字 =ARRAYFORMULA(LET(x,REGEXEXTRACT(INDIRECT("R[-1]C",false),"(\D+)(\d+)"),JOIN(,IFERROR(x+1,x))))
ま、こんな需要があるかはわかりませんがｗ
改行を 別の区切り文字に置換する
\n ・・・ 改行
改行は \n です。たまに Webサイトをコピペしたセルだと
\r や \r\n
が混じることもありますが、例外的なケースなので基本は \n と理解して問題ないです。
たとえば 改行を カンマに置換したい場合は
検索 \n
置換後の文字 ,
こうなるわけです。
文末に入ってる余計な改行を削除する
\n+$ ・・・ 文末にある 改行の1回以上の繰り返し
これまでに登場した \n 改行、 $ 文末 に、
+ 前の文字の1回以上の繰り返し
を組み合わせると、Webページからのコピペなんかでたまに発生する文末の無断な改行を削除ができるようになります。
削除は 置換文字に何も入れず、空白に置換するという処理をすればOK。
検索 \n+$
置換後の文字 (なにもいれない）
文頭に余計な改行があるケースでも、同様の方法で対処が可能です。
「または」で検索・置換する
| ・・・ または を意味する 例 りんご|ばなな
正規表現が使えると OR検索、置換ができるのも魅力です。
〇〇または△△または■■ という検索、置換をしたい場合は、
〇〇|△△|■■
このように 間に | （パイプ）を挟みます。
たとえば、りんご、ばなな、ぶどうを ✖✖✖ と伏字にしたい場合は
検索 りんご|バナナ|ぶどう
置換後の文字 ✖✖✖
このように 複数ワードを一括置換できます。
注意点としては、置換後の文字は1つしか指定できない点。
りんごをアップルに、バナナをバナーヌに ぶどうを グレープ に、といった変換リストをもとにしたような処理は、関数かGASで繰り返し処理を回さないと出来ません。
正規表現は沼
もう少し 正規表現を使った 検索と置換の 基本があるんですが、長くなってしまったので今回はここまでとしましょう。
正規表現について もう少し知りたい！という方は、以下のページや 検索して自分がわかりやすいと感じるサイトを参考にしてみてください。
次回は正規表現と組み合わせた検索と置換 のさらにディープな使い方に入っていきましょう！
いいなと思ったら応援しよう！
