【エクセル】スペースの全角・半角を関数で統一する置換テクニック

komatta

今回もエクセルネタを書かせていただきます。古屋です。

お題は置換方法ということですが、ショートカット Ctrl +で1つ。もう一つは、関数を使う方法。この2つがサクッとやれるお手軽なテクニックです。

エクセルで空白や空白以外のセルを一気にまとめて置換する方法
Excelを使っていて飛び飛びになっている空白を埋めたいとき、または飛び飛びになっている値を全て特定の文字列に置換したいときなどありますよね。そんな時に便利なジャンプ機能とその使い方について紹介をします。

こちらの記事でも、置換関連の情報を紹介させていただいております。

スポンサーリンク

スペースの全角と半角の混在は迷惑!?

スペースの全角・半角で困ったことはありませんか?

複数人が入力業務に携わると、スペースだけでなく英字や数字までもバラバラ。そんなお困りごとを解決できる置換方法を紹介いたします。

まず、全角と半角のスペースが混在している場合のデメリット。

  • 見栄えが悪い
  • SUMIF関数で検索できない
  • VLOOKUP関数で検索できない

いずれも、検索集計系の関数では不都合がありますね。例えばこんなリストの場合。

2016-09-04_11h48_48

上の画像では、佐藤しおりさんの金額を検索しようとしています。

ところが、B列の検索列データには全角のスペースや半角のスペースが苗字と名前の間に入っていたり、いなかったりしています。

肝心の佐藤しおりさんのセルには、全角スペースが入っています。これではSUMIF関数の検索に引っかからず、結果は\0と表示されてしまいます。検索系関数では不都合

冒頭でも述べた通り、複数の人が名前の入力をしたために様々なスペースが混在する現象はよくあることです。

データが数人程度なら、サッと直せますが100人、200人だったらゾッとしますね。

次の見出しで、全角スペースを半角スペースに統一する方法を紹介します。

全角スペースを半角スペースに統一するための3つの置換方法

複数の人がデータを手入力すると、注意書きしておいてもスペースを半角と全角まちまちに入力してしまう。

この問題を解決する為の、半角や全角に関する方法は3つ。

  1. Ctrl + H で全角スペースを半角スペースに置換
  2. SUBSTITUTE関数で全角スペースを半角スペースに置換
  3. ASC関数で半角に変換できる文字全て置換

以下のデータを全角・半角スペースの混在例として、上記3つの方法を紹介していきます。

全角半角の混在

Ctrl+Hで全角スペースを半角スペースに置換

Ctrl + H は検索と置換という機能のショートカットボタンです。この方法は、元データを直接修正しますので、元データは触れないor触りたくない場合は次の、SUBSTITUTE関数で全角スペースを半角スペースに置換を使ってください。

全角スペースを半角スペースに置換

 すべて置換(A) を押すと↓

置換完了

全角スペースを半角に統一することが出来ました。

SUBSTITUTE関数で全角スペースを半角スペースに置換

2016-09-04_12h04_28

=SUBSTITUTE(B2,” ”,” “)
B2セル内の全角スペースを半角にする

注意点として、全角スペースを入力する時に半角スペースを2回ではダメという事です。

必ず、入力を全角に変えてからスペースを押すようにして下さい。

ちなみに、第2引数と第3引数を入れ替えれば半角全角に置き換えることができます。

あと、以降紹介するASC関数やJIS関数も全角・半角に換える関数ですが、これらはセル内のすべての半角・全角を一括変換してしまう為、スペースだけを対象にする場合はSUBSTITUTE関数が適しています。

全角文字を半角に変換(ASC関数)

もう一つは、全角・半角の表記ゆれの問題です。特に関数で該当データを検索・表示させたいというニーズに対して、全角と半角の混在は致命的です。

ASC関数は、半角に出来るものは全て半角に変換してしまう優れものです。(ASC関数⇔JIS関数)

ASC関数

対象は漢字とひらがな以外です。

=ASC(B2)
B2セル内の文字を半角にする

注目すべきはC5セルのカタカナの部分。

スペースのみでなく、半角文字のものは全て半角に変換されています。

住所録でもよくある問題ですが、英字・数字・スペースと全角と半角の混在は見栄え悪くて、宛名ラベルを作成するときに最も面倒な作業と言えます。

スペースを削除する

そもそもデータとして、スペースが不要な場合もあるかと思います。そんな時にも

  • Ctrl + H でスペースを削除
  • SUBSTITUTE関数でスペースを削除

この2つの方法が便利かと思います。

前者はデータ自体を直接修正。後者は元データと修正後データが共存することになります。それぞれのデータの特長を理解して、使い分ける必要があります。

Ctrl + H でスペースを削除

修正したいデータ列を選択して、スペースを削除します。

Ctrl + H を押して、検索と置換のウィンドウを出します。

検索と置換

上記の設定で、選択範囲内の全角スペースと半角スペースの両方が削除できます。

SUBSTITUTE関数でスペースを削除

SUBSTITUTE関数でスペースを削除

=SUBSTITUTE(SUBSTITUTE(B2,” “,””),”  “,””)
B2セル内の半角を消去、さらに全角も消去する

この様な意味になります。

こんな時も、SUBSTITUTE関数が使えるんですね。半角・全角スペース両方に対応させると、入れ子になり少し複雑になりますが、便利ですので覚えておくと良いですね。

まとめ

以上の構成でお送りしてきました。

置換のネタは、結構便利で個人的には顧客情報とデータベースの管理に使っています。

全てを自分が入力するのであればこの様な問題はありませんが、組織で働いている以上は複数人で作業することが多いかと思います。

これで、「半角で入力してっていつも言ってるでしょ!?」なんて細かいこと言わなくてすみますね(笑)

それでは、便利で楽しいエクセルライフをお送を。

タイトルとURLをコピーしました