Googleスプレッドシートの 検索と置換シリーズ 2回目です。

単に 指定した文字を検索したり置換するだけではない、応用的な使い方を中心に紹介しています。

前回は Googleスプレッドシートの 3つの検索と置換、そしてExcelとの比較、最後に 正規表現を使った検索と置換の基本を書きました。

Googleスプレッドシート 検索と置換で出来る実用的な 小ネタ3選

今回は 正規表現を使った検索と置換の続きを中心に書いていきます。

でも、その前に 正規表現を使わずとも検索と置換を活用できる、ちょっと実用的な小ネタを3つ紹介しましょう。

検索と置換で チェックボックスを一括で切り替える

これは大したネタではありませんが、チェックボックスの一括切り替えを検索と置換で行えます。

これは Googleスプレッドシートのチェックボックスの中身が、基本は TRUEとFALSEであることを理解していれば思いつきますね。

注意点としては、チェックボックス以外の 文字列である TRUEやFALSEも置換してしまう点。たとえば範囲内のセルに 「TRUE LOVE」なんて文字があった場合は、「FALSE LOVE」（偽物の愛？）に置き換わってしまいますｗ

それを避ける為に「完全に一致するセルを検索」にチェックを入れるとよいでしょう。（あまり無いケースですが、これでもセルに単体でTRUEやFALSEという文字だけが入ってる場合は影響あります）

人によっては便利と感じるかもしれませんが、ちょっと面倒ですね。

ぶっちゃけチェックボックスの一括オン・オフ切り替えは、シート単位なら以前紹介した スペースキーを使う方法をおススメします。

複数のシートに チェックボックスがあるような スプレッドシートで、全シートのチェックボックスをオフにしたいといった場合は、この検索と置換による切り替えが効果的です。

検索と置換で 数式の再計算

Excelには「再計算」を実行するボタンがありますが、Googleスプレッドシートにはシート上の計算を止める（手動にする）や、再計算を実行するような機能がありません。

さすがに計算させないという方法はありませんが、Googleスプレッドシートでの再計算実行は「検索と置換」を使うことで代替できます。

そもそも、どんな時に再計算が必要となるか？

って話ですが、import〇〇系関数（importrange や importhtml, importxml）は、元データが更新されても 反映に時間がかかる時があるんです。そんな時、 再計算で最新データを取得したい！ってなります。

他には、ちょっと特殊な 別シートを参照するケース。

参照先のシートが追加（もしくはシート名変更）される前に、先にそのまだ存在しないシートを参照する式が作成されている場合です。

先に、これから追加される予定のシート「後から追加」シートを参照する式を用意しちゃうと・・・

後から 参照するシートが追加されても、一度エラーになったものは解消されず、エラーのままとなってしまいます。これはF5更新でも解消されません。

解決方法として、一度セルを編集モードにしてエンターで再確定するか、一度Deleteで削除してから 戻るボタン（Ctrl+z）で 処理を戻すことで 再計算をさせる方法 もありますが、ちょっと面倒ですね。

一気にやるなら 「検索と置換」がベストです。

このように イコールをイコールに置換することで、一気に再計算が走ります。

ただし重い式がいっぱい入ったスプレッドシートを全シート 再計算させると、結構な負荷となるので、その点は注意が必要です。

検索と置換で 値貼付けのURLをハイパーリンクにする

結構あるあるだと思うんですが、URLが画像左のような状態になって困ることありませんか？

上の画像、右の青字で下線のあるハイパーリンク状態のURLであれば、マウスをあてればプレビューが表示され、クリックでサイトを開くことができます。

しかし、左の 「ハイパーリンクになっていない」アドレスだと、マウスによるクリックでサイトを開くことが出来ません。（プレビューも出ません）

URLの入った複数セルを値貼付けした場合、このようなハイパーリンクになっていないURLの入ったセルが出来てしまうのですが、これをハイパーリンク状態にするにはどうすれば良いか？

先ほどの数式の再計算と同様に、一度セルを編集モードにしてエンターで確定する方法もありますが、これだと1セルずつひたすら繰り返す必要があります。

Googleスプレッドシートは エンターで編集モードになり、エンター確定で一つ下に移動するので、ひたすらエンター連打でいけるんですが・・・、件数が多いと自分が単純作業ロボになった気分です。

これも実は 検索と置換で一発解決できます。

URLは 通常 https（またはhttp）で始まるので、単純にそのうちの1文字

h を h に置換するだけです。

これだけでエンターで再確定したのと同じ状態となり、URLの一括ハイパーリンク化ができます。

正規表現を使わずに出来る、実用的な検索と置換の応用ネタ3つでした～。

検索と置換 で使える正規表現の基本 2（空白でないセルを検索で出来ること）

それでは前回の続き、検索と置換で使える 正規表現の続きを書いていきましょう。

前回登場した正規表現は以下になります。

正規表現は初学者はなかなかピンとこないんですよね。

Googleの「検索と置換」の公式サイトでも 正規表現が幾つか紹介されてますが、実際使ってみないとなかなか腹落ちしないもんです。

プログラミングをやらない人が Googleスプレッドシートの「検索と置換」を正規表現の入り口としてみるのは、結構おススメです。

空白ではないセルを検索する

正規表現を学ぶ際、一番最初に登場するのが . （ドット）です。

. は なんらかの1文字をあらわすもので、Excel関数に慣れている人なら ワイルドカードの ? のようなものと言うとわかりやすいでしょうか？

ちなみに ?も *も 正規表現では ワイルドカードとはまったく違う意味合いになります。注意しましょう。

*の0文字以上の繰り返しという考え方がわからない人もいるかと思うので、上の表で少し解説をしておきましょう。

上の表は REGEXEXTRACT関数で、正規表現がどの部分にヒットしたかを確認したものです。

A2セルの 田中 に対しては、「田中.」や「田中.+」は一致せずエラーを返しているのがわかりますね。これは 田中の後ろに何も無いからです。

一方、「田中.*」であれば、田中の後ろが0文字でも一致となるので、エラーにはならず「田中」を返しています。

また、「田中太郎」の場合は、「田中.」だと田中の後ろは1文字しか見ない為、「田中太」になってしまいます。

.+や.*であれば任意の文字の繰り返しを表すので、「田中太郎」に一致となるわけです。

というわけで、.* だと空白セルも検索対象になってしまうので、空白ではないセル（なにかしら入力されたセル）を検索する際は . または .+ を使うことになります。

実際に空白以外のセルを検索してみましょう。

上の動画ですが、 塗りつぶしてある3つのセルは 数式が入っており、C3:C4は数式が空文字を返しています。この数式が返した空文字は 対象外（空白と見なさされている）のがわかりますね。

さらに、枠で囲んでいる B3セルは 見た目ではわかりませんが、文字色を白にした「い」という文字が仕込んであります。こちらは人の目だと見落としてしまいがちですが、検索と置換ではしっかり 空白ではないセルとして検知しています。

空白ではないセルを検索することが出来ました。

検索と置換で 数式が入ったセル以外をクリアする

空白ではないセルを検索できると、どんなメリットがあるのか？

1つ目はこの 式が入ったセル以外をクリアする処理です。

上のように 色付けしたセルに数式が入ったシートで 式以外のセルだけ値を一括クリアしたい時。

手動で選択してDeleteでもいいですが、セル数が多かったり、式が入ったセルがどれかパッと見でわからなかったり、複雑に色々な個所に数式が仕込んであったりすると、手動だと面倒ですし誤操作もありそうですよね。

この数式が入ったセル以外の一括クリアは「GASを使わないと無理」なんて言う人もいますが、実は 検索と置換で処理できちゃいます！

やり方は 先ほどの 空白でないセルを検索する流れと一緒。

これだけですｗ

前回も触れましたが、数式の結果は検索対象にはなっても置換できません。

つまり、単順に正規表現で空白ではないセルを空白に置換すれば、数式の入ったセル以外（値を入力したセル）だけが空白となるわけです。

超便利ですね！

悪用厳禁！ 検索と置換で非表示のセルやシートをチラ見

たとえば共有されたシートに 非表示状態で保護された列（行）やシートがあった場合、中身を見ようとして再表示させようとしても、このようにアラートが出てしまいます。権限がないので見れないってことです。

でも、実は非表示にした行や列の 隠されたセルの文字も、この空白以外のセルを検索する方法で覗き見が出来ちゃいます。

相手に見せたくないってことで非表示状態で保護された D列ですが、検索と置換で 秘密の文字が 検索結果に表示されちゃってますねｗ

では？保護した状態の非表示のシートの場合はどうか？

こちらは残念ながら、編集者の場合はこのようにエラーとなります。

これは 検索と置換で「すべてのシート」を検索すると、検索したセルをアクティブにする挙動の際に、非表示のシートを表示してしまうという仕様の為です。

ここでわざわざ「編集者の場合は」と書いたのは、実は本来編集者より低い権限の 閲覧者ならこの制限すら突破してしまう からです。

Googleスプレッドシートを共有する際、もっともセキュリティレベルの高い共有は 閲覧者として共有した上で、さらに共有設定から ダウンロード、印刷、コピーを禁止したものです。

実はこの チェックを外しておかないと、閲覧のみで共有したスプレッドシートに見せたくない 非表示シートがあっても、相手は見ることができちゃいます。

閲覧者側は、ダウンロードやスプレドシートのコピーを作成して 隠されたシートを見ることも出来ますし、実は importrangeで 非表示シートを自分のスプレッドシートに出力し、ほぼリアルタイムの状態で見る方法もあります。

しかし、設定のチェックを外して 閲覧者のダウンロード、コピー、印刷を禁止した場合は

このように 閲覧者のコピーやダウンロードはもちろん、importrangeを使った情報取得も防止できます。

しかし、これだけガードを固めても・・・

このように 空白以外のセルを検索を使えば、非表示にした秘密シートの中身が 閲覧者は 見れてしまうわけです。（セル内の文字数が多い場合はその一部ではありますが）

もちろん編集者に対してはimportrangeを制限できません。でも、閲覧者にしても 検索と置換で覗き見出来ちゃう。。

スプレッドシートを共有した時点で、そのシート内の情報は完全に隠すことは出来ないってことです。

この仕様は問題ありと思いますが、とりあえず現状では仕方ありません。共有したスプレッドシートに安全な場所はないって思いましょう。

※あくまでも共有する側への注意喚起の内容です。悪用はダメ！

検索と置換 で使える正規表現の基本 3（文字列操作を掘り下げる）

基本の正規表現について、もう少し掘り下げていきましょう。

任意の〇文字、〇文字以上、〇文字以下という指定

. が 任意の1文字、 .+が 任意の1文字以上の繰り返し。

これはわかりましたが、じゃあ 〇文字という文字数が決まってるものを検索したい時はどうすればよいでしょうか？

二文字や三文字なら .. とか　… と書いてもいいですが、10文字とかになってくると厳しいです。

ここで活用できるのが {n} という正規表現です。

たとえば .{10} として検索した場合、10文字（以上）が入っているセルがヒットします。

なぜ10文字じゃないのに11文字や13文字のセルがヒットするのか？それは、セル内の文字列が部分的に 10文字にヒットするからです。11文字だったら最後の1文字を除いた部分が10文字として検索にヒットしているわけです。

たとえば 10文字ちょうどのセルを検索したい場合、 10文字以下 としたい場合は、「完全に一致するセルを検索」にチェックを付ける必要があります。

たとえば 

のセルを検索しようとした場合、A4 セルの abcdefghijklm も部分的に切り出すと

と判定できる為、検索にヒットしてしまします。

「完全に一致するセルを検索」にチェックを付ければ、A4セルの13文字は対象となりません。

LEN関数を使って文字数をカウントしてFILTER関数で抽出する方法もありますが、このように検索と置換で文字数を指定した検索も出来るのです。

先頭（末尾）の n文字だけ削除する

この文字数を指定した検索を置換に応用すると、たとえば先頭（末尾）のn文字だけ一括削除。といった、シート関数のREPLACE関数のようなことが出来ます。

たとえば 各セルに 南斗五車星 が1人ずつ入っていたとして。（わけのわからない設定ですが・・・）

最初は カッコいいと思ってつけた「風の」とか「山の」が、今の時代だとちょっと厨二っぽくて恥ずかしいんで削除したい。ってなった場合

このようにすることで、先頭の2文字を一括削除できます。

末尾の場合は .{2}$ で同様に可能です。

先頭から 最初の「の」まで削除したい

上のように 文字数が２で固定されている場合は理解できましたね。

それでは、文字数が固定ではなく先頭から最初の「の」まで削除したい時はどうすればよいでしょうか？

このように 「の」までが1文字だったり、2文字だったり、4文字のものもある場合です。

単純に ^.+の　で出来そうに思うかもしれませんが

このように  「盾の勇者の成り上がり」 や 「七つの大罪 黙示録の四騎士」など「の」が2つ以上登場する場合、一番後ろの「の」までがマッチして削除されてしまいます。

これが正規表現の「暴食」ならぬ「貪欲」マッチ(最長一致）というやつです。

単に 「.+」の だと、なるべく長い範囲を取得しようとして 一番最後に見つかった「の」までをマッチした範囲として取得してしまいます。

これを非貪欲（最短での一致）とする為には ?をつけて  「.+?の」 とする必要があります。

REGEXEXTRACT関数で取り出してみると、上のような結果となります。

これは 過去noteで EXCELのTEXTBEFORE / TEXTAFTER の代替式を考える際にも、簡単に説明しています。

興味がある方は、他のサイトも見てしっかり調べることをおススメします。

とりあえずは、最短での一致を取得したい場合は ? をつける ってことだけ、覚えておきましょう。

先頭（末尾）の n文字だけを残す

では逆に 検索でヒットした 箇所を削除するのではなく、セル内の検索でヒットした箇所（先頭の2文字）だけを残して他を削除したい場合はどうすればよいでしょうか？

幾つかやり方はありますが、ここでは上のやり方の応用で書けるキャプチャグループを使う方法を紹介しましょう。

先ほどの先頭から2文字の削除で使った　^.{2} の任意の文字の繰り返し部分をカッコで括って 　^(.{2}) とすることでキャプチャグループ化して置換後に利用できるようにしています。

ただ、これだけだと 残りの部分を削除することが出来ないので、後ろに .*$をつけることで残りの部分にもマッチさせて、置換の際にそちらは削除しています。

.+$ ではなく .*$ としたのは、先頭の2文字の後に何も文字がない可能性があるからですが、 ^(.{2}).+$　とした場合でも、最低3文字以上のセルが検索でヒットする形になるので、2文字以下のセルはそのまま置換対象外となり、結局今回の場合は同じ結果になるんで気にしなくていいんですが、挙動の違いは理解しておくと良いです。

セル内の文字列の先頭と末尾のそれぞれに 文字を追加する

このキャプチャグループが使えるようになると、セル内の文字の先頭・末尾の両方に一発で文字を追加する、なんてことも出来るようになります。

人の名前がセルに入っていた場合、各セルの

先頭に　拝啓、
末尾に　様

を付けたい場合

このように設定すれば良いです。

セルの中身を丸ごとをキャプチャ、$1として置換後に再利用することで、一発で前後に文字を挿入できました。

後になってから「やっぱり戻したい」って場合は、逆をやれば「 拝啓、」と「様」の間の元の文字列を取り出せます。

検索と置換を使った、文字列操作に自信がついてきたんじゃないでしょうか？

数値だろうが数式だろうが一発で 加算する

今回登場した *（0回以上の繰り返し）と、この前後に文字挿入を応用すると、前回のnoteで紹介した セルの一発 加算（減算でも乗算でもなんでもよい）が、もう少しスマートに出来るようになります。

前回は

このように先頭に =2* を差し込む方法で、範囲内の数値を一気に 2倍にしました。

でも、出来れば元の数字が先頭に来た方が分かりやすいですよね？

つまり

=2 * (元の数字）　よりも
=(元の数字) *2

と置換された方が、後で見たときに元の数値を2倍したんだなとわかりやすい記述になります。

さらにこの方法だと =が入った数式が返す数値には計算が適用されません。

数式の結果は置換できないので、数式内も検索にチェックを入れて再度同じ置換をすると、

=2*=2*30

と イコールが二重で先頭に入ってしまいエラーとなります。

このような 数値と数式が混在するケースでも、連続で 乗算（またはその他の計算）を検索と置換でやるにはどうすればよいか？

その方法がコチラです。

ポイントは =* の箇所。

これで、先頭に =がある場合は置換の際に削除、先頭に =がなくても検索にはヒットするので、セルの中身が数式でも数値でも対応できる 検索と置換になっています。

こんな感じのデータで試してみましょう。

数値だろうが計算式だろうが関数が入っていようが、繰り返し加算されているのがわかりますね。

需要があるかはわかりませんが、こんな計算も検索と置換で出来ちゃうってネタでした。

セル内改行がある場合の正規表現

. は任意の1文字と書きましたが、実はこれ 改行コードにマッチしません。

だからセル内に改行があると、上に書いた通りにうまくいかないケースが出てきます。

たとえば

というセル内で改行されたセルがあった場合、(.+) で拝啓、様置換をすると

と改行で区切られた文章単位でマッチして、それぞれ置換されてしまいます。

では ^(.+)$ だったらどうか？

こんどは 改行入りのセルは 検索にマッチしない、という結果になってしまいます。

こんな時、REGEX関数系なら sフラグを使って シングルライン（ドットオール）モードに切り替えることで、. を改行にもマッチさせるという技が使えます。

当noteでも過去に何回か登場した正規表現テクです。

しかし、残念ながら 検索と置換の 正規表現では マルチラインモードや ドットオールモードに切り替えるフラグは使えません。

では、どうするか？

上のWWWクリエイターズさんの 記事内にも記載がありますが、

これらを. の替わりに使います。

この2つをどう組み合わせるか？ 前回 または（OR）の記述で | パイプ を使った方法を紹介しましたが、1文字の場合は [ 文字 ] を使って ORを表す方法があります。

基本といいつつ、この辺りを飛ばしてましたねｗ

たとえば大文字アルファベット2文字なら
[A-Z]{2}

8文字以上の数値が含まれるセルを検索したい場合は
[0-9]{8,}

といった表記ができるわけです。

入力フォームなんかで検証に使う時は、このような正規表現の理解が重要です。

話がそれましたが、.+ が通用しない 改行を含むセルの 全文に一致させる為には \s と \S どちらかの1文字以上の繰り返しを取得すればよいので

このような記述になります。

というわけで、改行入りセルを含む範囲に 拝啓、様 置換をするには

このように . ではなく  [\s\S] を使えばよいってことです。

前回と今回 紹介した 検索と置換 で正規表現を使う方法は、いずれも . を[\s\S]に置き換えることでセル内改行にも対応できます。

正規表現はやっぱ 沼

だいぶ簡略化してるんですが、正規表現で出来ることがあまりにも多いので、今回も正規表現を使った 検索と置換の紹介したいネタが終わりませんでした。

次回はそろそろ応用編、正規表現を使った検索と置換で

・〇〇を含まないセルを検索する
・複数単語を全て含むセルを検索する
・GASなしで改行置換を実現する
・文字数をキープした状態で ✖✖✖の伏字にする

この辺りに挑戦してみたいと思います。

正規表現って難しいなーって人は、細かいことは無視して

「こういうことやりたい時は、検索と置換でこうすればいいのか」

ってとこだけ覚えてコピペ活用でもいいと思います。使ってればそのうち理解は追いついてきます。

そもそも、mirの説明が間違ってる（漏れがある）可能性もあるのでｗ

正規表現はホント難しくて、どうしても対応漏れがあったりするんで、もし記載内容に不備や誤りがあったらお知らせください。