ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.003

余分なスペースを削除してスペースを全角または半角に揃える 《SUBSTITUTE・TRIM》

2003/2007/2010/2013/2016/2019

Excelではさまざまな処理において全角/半角を区別する、つまり全角と半角とでは別データを見なすケースが多く、データ処理においては注意する点となっています。
例えばデータの担当者列などで、苗字と名前の間のスペースが全角で入力されている「森 花子」さんと半角スペースの「森 花子」さんとでは別人となってしまいます。

今回のお問い合わせは、苗字と名前の間のスペースが全角と半角が混じっている可能性があるということなので、苗字と名前の間のスペースを半角にし、その他の余分なスペースを削除して、テータをきれいに揃えたいというものです。
この場合、すべてのスペースを半角に揃えることと、余分なスペースを省くことが必要となります。
2つ必要となる関数を1つずつご案内します。

では、A列セルA2より氏名のデータが入力されているとします。
スペースを半角に揃えるのは、SUBSTITUTE 関数(分類: 文字列操作)を使用します。

SUBSTITUTE 関数 は、対象の文字列のある文字列を別の文字列に置換する関数です。
セルA2のデータ(引数1つ目)の全角スペース(引数2つ目)を半角スペース(引数3つ目)に置換する場合、式は次のようになります。
スペースも文字列ですので、前後にダブルクォーテーションを付けてあげます。

  =SUBSTITUTE(A2," "," ")

これで、すべてのスペースが半角に揃いました。
全角文字列を半角文字列に変換するのは ASC 関数 がありますが、今回氏名のデータということで、「ハナコ」さんが「ハナコ」さんと半角になってしまうため、使用していません。

では次に、余分なスペースを省くのは TRIM 関数(分類: 文字列操作)を使用します。
引数は1つ、対象の文字列(のセル)を指定します。

  =TRIM(A2)

TRIM 関数で取り除く余分なスペースというのは、データ前後のスペース、間に入っている複数のスペースの2番目以降のスペースです。

では、この2つの関数を合体してみましょう。
実はどちらから処理しても同じ結果となります。

全角スペースを半角スペースに置換したデータの余分なスペースを除く場合は

  =TRIM(SUBSTITUTE(A2," "," "))

SUBSTITUTE関数

余分なスペースを除いたデータの全角スペースを半角スペースに置換する場合は

  =SUBSTITUTE(TRIM(A2)," "," ")

どちらにしても、結果は次のようになります。

スペース揃え

ヘルプmemo

<TRIM 関数>(分類: 文字列操作)
指定した文字列から不要なスペースを削除した結果を返します。

数式:
=TRIM(文字列)
引数:
・[文字列] ...不要なスペースを削除したい文字列、またはセル参照で指定

<SUBSTITUTE 関数>(分類: 文字列操作)
文字列中の指定した文字列を新しい文字列に置換して返します。

数式:
=SUBSTITUTE(文字列, 検索文字列, 置換文字列, 置換対象)
引数:
・[文字列] ...置換の対象である文字列、またはセル参照で指定
・[検索文字列] ...指定した文字列の中での、置換対象となる文字列を指定
・[置換文字列] ...置換する文字列を指定
・[置換対象] ...省略すると対象をすべて置換、数値nを指定するとn番目の対象を置換 (省略可)
twitter hatena line pocket

関連ヘルプ

7桁で入力されている郵便番号のデータにハイフォンを挿入したい 《REPLACE》
左から(右から・途中から)決まった文字数の文字列を取り出す 《LEFT・RIGHT・MID》
全角の英数字を半角にするには、またはその逆 《ASC・JIS》
数字文字列を数値にする(関数編) 《VALUE》
別々のセルに入っているデータを結合して(つなげて)表示する 《アンパサンド・CONCAT (CONCATENATE) 》
2つの日付の期間が何年かを求める 《DATEDIF》
セルに入力されている数値を時刻の表示にする 《TIME》
時刻の「8:30」を「8.5」と数値に変換する
循環参照を解決するために
条件を満たすセルの個数を数える 《COUNTIF》
フィルターで抽出されたデータの件数、合計をセルに表示したい 《SUBTOTAL》
検索条件に一致するデータの合計を求める 《SUMIF》
負の小数値を合計したときに計算結果に誤差が生じる
カウントする関数が何をカウントするか 《COUNT・COUNTA・COUNTBLANK》
=のあとに+が表示された「=+」から始まる数式の意味とは
数式で参照しているセルを変更しても、結果が更新されない
セルに計算結果ではなく数式が表示されている
先頭のシングル クォーテーション、アポストロフィー(')を取るには(関数編) 《SUBSTITUTE》
10進数の数値を2進数と16進数に変換するには 《DEC2BIN・DEC2HEX》
月の日数、月末を求める 《EOMONTH》