スプレッドシートを使っているときに、数式の中で正規表現を使って置換を行いたいというときがあります。
ここではその方法について解説しています。
REGEXREPLACE関数の使い方
スプレッドシートを使っているときに、数式の中で正規表現を使って置換を行いたいときは「REGEXREPLACE関数」を使います。
基本構文は次のようになっています。
REGEXREPLACE(対象の文字列, 正規表現, 置換文字列)REGEXREPLACE関数を使うときに、正規表現に一致する文字列が複数ある場合に、全てを置き換えたいときにgフラグなどのオプションを指定する必要はありません（というかできません）
JavaScritpなどの正規表現で、指定した内容に一致する文字列が複数ある場合はgフラグを使いますが、これがいらないということです。
str.replace(/置換前の文字列/g, "置換後の文字列");実例｜REGEXREPLACE関数
元データ
例えば以下のようにV列の「依頼連絡済み」に複数の名前が入っているデータがあるとします。
（名前はダミーです）
REGEXREPLACEによる置換
名前の冒頭に（役員1）や（３）といった文字列がついています。REGEXREPLACE関数を使って、これを全て・に置き換えます。
置き換えはとても簡単で以下の数式を記述するだけです。
=REGEXREPLACE(V15,"\(.*?\)","・")これは、V15セルの値のうち、指定した正規表現にマッチする文字列を全て「・」に置き換えるという指示です。
バックスラッシュはエスケープで、カッコを正規表現ではなく文字列としてのカッコとして指定しています。
また、「.*?」の「?」は最短一致を指示しています。「?」がないと最長一致になり、最初の「 ( 」から最後の「 ) 」までが1つの置換対象として認識されてしまいます。
実行結果は以下のようになります。
改行する｜CHAR(10)
置換を使って改行を挿入する方法
これは補足ですが、もしデータを改行したい場合はCHAR(10)を使います。
例えば、上記の例では名前がひとつながりになっています。
これを改行するには以下のように記述します。
=if(V15="","",right(REGEXREPLACE(V15,"\(.*?\)",CHAR(10)&"・"),len(REGEXREPLACE(V15,"\(.*?\)",CHAR(10)&"・"))-1))
数式の解説
ちょっと数式が長いですが、やってることは単純です。
まず以下の数式が一番のメインになります。
REGEXREPLACE(V15,"\(.*?\)",CHAR(10)&"・")これで、正規表現にマッチした文字列をCHAR(10)&"・"に置き換えます。
このままだと一番最初のデータにも改行が入ってしまいます。最初の改行は除外したいので、right関数を使って、最初の文字以外の結果を返しています。
right(REGEXREPLACE(V15,"\(.*?\)",CHAR(10)&"・"),len(REGEXREPLACE(V15,"\(.*?\)",CHAR(10)&"・"))-1))
このままだと、空白セルを置換しようとしたときにエラーなるので、if文を使って空白セルのときは何もしない指示をしています。
=if(V15="","",right(REGEXREPLACE(V15,"\(.*?\)",CHAR(10)&"・"),len(REGEXREPLACE(V15,"\(.*?\)",CHAR(10)&"・"))-1))数式の中を正規表現で置換する方法
ここではREGEXREPLACE関数を使って、数式の中で正規表現を使って置換する方法を紹介しました。
そうではなく、数式自体を正規表現を使って置換したいという場合は下記をご参考ください。
【スプシ】正規表現を使って文字列を置換する方法｜数式内の元の値はそのままで変換させない方法（スプレッドシートのカッコと$ドルマークとは何か？）
AppScriptで置換する方法
数式ではなく、AppScriptのプログラムを使って置換することもできます。
function replace() {
const targetSsId = "スプレッドシートのID";
const ss = SpreadsheetApp.openById(targetSsId);
const sheet = ss.getSheetByName("シート名");
//対象データの取得
let startRow = 14;
let nameCol = 22;
let endRow = ankenSheet.getRange( 7, 15 ).getNextDataCell( SpreadsheetApp.Direction.DOWN ).getRow();
let pasteCol = 21;
let copyRange = sheet.getRange( startRow, nameCol, endRow, 1 );
let pasteRange = sheet.getRange( startRow, pasteCol, endRow, 1 );
//既存のデータを削除
pasteRange.clearContent();
//正規表現で置換
let values = copyRange.getValues();
values.forEach(( val, index ) => {
if ( !!val[0] ){
repVal = val[0].replace( /\(.*?\)/g, "・" );
sheet.getRange( startRow + index, pasteCol ).setValue( repVal );
}
})
//依頼連絡済みの（）を・に置換しました。
};
//対象データの取得 の部分は使っているデータに合わせてよしなに変更してください。
