Excelで空白を削除する関数

SUBSTITUTE関数で文字間のスペースを削除する方法や、TRIM関数で先頭・末尾のスペースを一括削除する方法をご紹介します。空白を含む不要な行をまとめて削除する機能や、関数で空白行を上に詰める方法も解説しているので、ぜひ参考にしてみてください。

文字間のスペースを削除する関数

SUBSTITUTE関数は検索対象の文字列から、検索文字列を検索して新しい文字列に置換します。

例えば SUBSTITUTE(A2,”　”,””) なら全角スペースを検索し、何もない状態に置換します。全角文字と半角文字は区別されるため、半角スペースは半角で入力してください。

SUBSTITUTE関数の構成要素：(文字列, 検索文字列, 新しい文字列, [置換対象])

スペースは削除するだけでなく、句読点やカンマに置き換えることも可能です。

例えば SUBSTITUTE(A2,”　”,”-“) なら全角スペースを検索し、ハイフンに置換します。これを実行すると「0120　12　345」は、「0120-12-345」になります。

数式内に文字列や記号を指定する場合、二重引用符（”文字列”）が必要です。

文字列内の特定のスペースだけを置換する場合は、置換対象に番号を指定します。

例えば SUBSTITUTE(A2,”　”,””,2) なら、文字列に含まれる 2 番目のスペースを新しい文字列に置換します。置換対象を省略した場合は、すべての検索文字列が置換されます。

TRIM関数は単語間のスペースを 1 つ残し、不要なスペースをすべて削除します。

例の場合なら単語間の繰り返しのスペース、先頭と末尾のスーペース、が削除対象です。TRIM関数では全角・半角に関わらず、不要なスペースがすべて削除されます。

TRIM関数の構成要素：(文字列)

検索対象の範囲を選択し、[Ctrl]+[H] キーを押して[検索と置換]を開きます。

検索する文字列には空白を入力し、置換後の文字列はそのまま何も入力しないでください。[すべて置換]をクリックすると、範囲内に含まれる空白がすべて削除されます。

置換後の文字列に区切り文字を指定すると、空白を句読点やカンマに置き換え可能です。

空白行を含む表などの範囲を、いったん全て選択してください。

次に[ホーム]タブの[検索と選択]から、[条件を選択してジャンプ]を開きます。

ジャンプ機能を開くショートカットキー：[Ctrl]+[G]

選択オプションから、[空白セル]にチェックを入れてください。

[OK]を押すと空白セルが選択状態になるので、右クリックメニューから[削除]をクリックします。

削除するセル単体を１段上に詰める場合は、[上方向にシフト]を選択します。

このとき行全体が削除されたわけではないため、A列は連続した記号のまま残ります。

空白セルの行全体を削除する場合は、[行全体]を選択してください。

このとき空白を含む行自体が削除されるので、A列の記号からは C と E がなくなります。

まず IF関数と ROW関を使って、空白でないセルに行番号を振ります。

例えば IF(B2=””,””,ROW()) はセルが空白なら空白を、空白でないなら行番号を返します。

IF関数の構成要素：(論理式, 真の場合の値, 偽の場合の値)

次に SMALL関数で、行番号を小さい順に並べます。

このときSMAL関数の順位にROW関数で 1 行目を指定しておくと、オートフィルでコピーしたときに値を小さい順に抽出可能です。

範囲は SMALL($A$2:$A$7,ROW(1:1)) のように、[F4]キーで固定しておきます。

SMALL関数の構成要素：(配列,順位)

最後に INDEX関数で、範囲から行番号と列番号がクロスするセルの値を抽出します。

例えば INDEX(B:B,$E2) なら、抽出されるのは参照範囲の 2 行目の値（りんご）です。範囲には１列のみを指定しているので、列番号に関しては省略しています。

INDEX関数の構成要素：(範囲, [行番号], [列番号])

INDEX関数の参照範囲は固定せずに、行番号の参照列のみを固定しています。

そのためオートフィルで数式をコピーするだけで、空白行を除く一連のデータを取得可能です。2 つの数式をまとめると、INDEX(B:B,SMALL($A$2:$A$7,ROW(1:1))) になります。