セルの文字列から先頭の空白を除去する方法を紹介します。
方法1: TRIM関数を利用する
TRIM関数を利用すると、セルの値の先頭の文字列を除去できます。
下図の表を用意します。
| (空白4つ)ぺんぎんクッキー |
| (空白6つ)らくだキャラメル |
| (空白4つ)しろくまアイス |
| (空白2つ)かるがもサブレ |
D3セルに次の数式を入力します。
C3セルの値の空白文字を取り除きます。
数式の入力を確定します。D3セルにC3セルの値から先頭の空白文字を取り除いた文字列が表示されます。
D3セルをコピーしてD列のほかの行にペーストします。他の行でも、C列のセルの文字列の先頭の空白が取り除けています。
セルの文字列の先頭の空白を取り除けました。
文字列の内部に空白がある場合
文字列の内部に空白がある場合の結果を確認します。C列の文字列を以下の文字列に変更します。
| (空白4つ)ぺんぎん(空白1つ)クッキー |
| (空白6つ)らくだ(空白4つ)キャラメル |
| (空白4つ)しろくま(空白4つ)アイス |
| (空白2つ)かるがも(空白10)サブレ |
結果は下図になります。D列の結果を確認すると、先頭の空白はすべての文字が削除され、
文字列内の空白は空白1文字を残して削除される動作であることが、確認できます。
方法2: Office Scriptsを利用する
先に紹介したTRIM関数を利用して、先頭の空白が削除されますが、内部の空白も1文字を残して削除されてしまいます。
内部の空白はそのまま残したい場合は、TRIM関数ではなく、Office Scriptsを利用する方法があります。
Office Scriptsを利用して正規表現の置換を使用して先頭の空白を除去する方法です。
次の正規表現で置換を実行すると先頭の空白を除去できます。
^ *
マッチしたパターンの文字列を "" (空文字)に置換します。
コード
Office Scriptsを作成し、以下のコードを記述します。
function main(workbook: ExcelScript.Workbook) {
let cell: ExcelScript.Range = workbook.getActiveCell();
let celltext: string = cell.getValue() as string;
let reg: RegExp = RegExp("^ *", "");
let resulttext = celltext.replace(reg,"");
cell.getOffsetRange(0, 1).setValue(resulttext);
}
解説
現在のカーソル位置のあるセル情報を取得し、セルのテキストを取得します。
詳しくは
こちらの記事を参照してください。
let cell: ExcelScript.Range = workbook.getActiveCell();
let celltext: string = cell.getValue() as string;
セルのテキストを
^ * の正規表現で "" (空文字)に置換します。
Office Scriptsでの正規表現の置換については
こちらの記事を参照してください。
let reg: RegExp = RegExp("^ *", "");
let resulttext = celltext.replace(reg,"");
置換したテキストをカーソルのあるセルの一つ右のセルに出力します。
cell.getOffsetRange(0, 1).setValue(resulttext);
実行結果
変換したいセルにカーソルを移動し、スクリプトを実行します。
1つ右側のセルに先頭の空白が除去された文字列が表示されます。
ほかのセルでも同じ操作を繰り返します。先頭の空白が除去されていますが、文字列中の空白はそのまま保持されていることがわかります。
セルの文字列から先頭の空白を除去することができました。
補足:範囲選択に対応する
上記のコードで、セルの先頭の空白を除去できましたが、一つずつセルを選択して実行する方法では、セルの数が増えると大変です。
範囲選択し、実行一回で右側のセルに先頭の空白を除去した値を出力したいです。
範囲選択に対応するにはコードを以下に変更します。
function main(workbook: ExcelScript.Workbook) {
let selectedRange:ExcelScript.Range = workbook.getSelectedRange();
let rowCount = selectedRange.getRowCount();
for (let i=0; i<rowCount; i++){
let cell: ExcelScript.Range = selectedRange.getCell(i,0);
let celltext: string = cell.getValue() as string;
let reg: RegExp = RegExp("^ *", "");
let resulttext = celltext.replace(reg, "");
cell.getOffsetRange(0, 1).setValue(resulttext);
}
}
解説
getSelectedRangeメソッドで選択範囲を取得し、getRowCountメソッドで選択範囲の行数を取得します。
let selectedRange:ExcelScript.Range = workbook.getSelectedRange();
let rowCount = selectedRange.getRowCount();
for ループで行数の数だけ処理します。
for (let i=0; i<rowCount; i++){
/* 略 */
}
選択範囲のi行目0列目のセルを取得します。
let cell: ExcelScript.Range = selectedRange.getCell(i,0);
以降は先ほどと同じ置換処理を実行し、1つ右側のセルに結果を表示します。
let celltext: string = cell.getValue() as string;
let reg: RegExp = RegExp("^ *", "");
let resulttext = celltext.replace(reg, "");
cell.getOffsetRange(0, 1).setValue(resulttext);
実行結果
変換したい値のセルを範囲選択します。選択後にスクリプトを実行します。
選択したセルの先頭の空白文字列を除去した値が右側のセルに出力されます。
補足:getValue()のパフォーマンス改善
getValue()の実行処理が重いため、選択するセル数が多くなると、getValue()の呼び出し回数が増え実行速度が低下する問題が発生します。
多くのセルが選択される状況の場合、ループ内でgetValuesを呼び出さないコードにしたほうが良いです。
以下のコードに変更します。
ループの手前で、getValues() メソッドを呼び出し、セルの値をまとめて取得して2次元配列に代入します。
詳細は
こちらの記事を参照してください。
コードが若干複雑になるため、個数が少ない状況であれば、先のコードのほうがわかりやすいかと思います。
function main(workbook: ExcelScript.Workbook) {
let selectedRange:ExcelScript.Range = workbook.getSelectedRange();
let values: string[][] = selectedRange.getValues() as string[][];
for (let i = 0; i < values.length; i++) {
let celltext: string = values[i][0];
let reg: RegExp = RegExp("^ *", "");
let resulttext = celltext.replace(reg, "");
let cell: ExcelScript.Range = selectedRange.getCell(i, 0);
cell.getOffsetRange(0, 1).setValue(resulttext);
}
}
著者
iPentec.com の代表。ハードウェア、サーバー投資、管理などを担当。
Office 365やデータベースの記事なども担当。
最終更新日: 2024-01-22
作成日: 2022-12-20
