データを処理するときに余分なスペースは都合が悪いので、削除したい・・・といったときの処理方法です。
関数で不要なスペースを削除する topへ
- 下図のように、数字の123の前や後ろまたは間にスペースが入ったデータがあります。
処理するデータは B列です。下図はスペースの位置を示すため前後にAとBを入れた説明のための図です。
これはスペースの位置をわかりやすく説明するためのもので、処理(計算)には使用しません。
- 種類とはスペースの種類で、Char(32) は半角スペース、Char(160)はWeb ページで HTML の実体参照
として使われているものです。
Char(10)はセル内改行で使われるものです。セルの配置を「折り返して全体を表示する」にしています。 - 3行目はスペースなし、4行目は前にスペースを1個、5行目は前にスペースを2個
6行目は後ろにスペースを1個、7行目は後ろにスペースを2個
8行目は間にスペースを1個、9行目は間にスペースを2個入れてあります。
10,11行目のスペースは Char(160)です。
12行目は1の後ろにChar(10)が入っています。 - Char(160)は↓の123を選択して、Excelへ貼り付けてみてください。
Char(160) Char(32) 1 2 3 1 2 3 - または、数式で="1"&CHAR(160)&"2"&CHAR(160)&"3" とすることもできます。このセルをコピーして、値貼り付けします。
- =CODE(MID(B8,2,1)) とすると、前の1と2の間のスペースは Char(160)であるのを確認できます。
後ろの1と2の間のスペースと通常の半角スペースですので Char(32) となっています。
- 種類とはスペースの種類で、Char(32) は半角スペース、Char(160)はWeb ページで HTML の実体参照
として使われているものです。
- 下図のC3セルは =TRIM(B3) 、D3セルは =CLEAN(B3) 、E3セルは =SUBSTITUTE(B3," ","")
と入力しています。
F,G列については以降で説明します。
スペースが取り除けた部分は薄いグレー、取り除けなかった部分は薄い黄色に塗りつぶしてあります。
スペースの位置や数が分かるようにフォントはMSゴシックにしてあります。
6〜7行目はすべて同じように見えますが、実は後ろにスペースがあり、異なっていますのでご注意ください。
- 上の図では6〜7行目の後ろのスペースが取り除けたか否かが分かりませんので、処理した結果の前後にAとBと付けたのが下の図です。
6、7行目の結果の違いが分かると思います。
- Trim関数は前後のスペースを取り除き、文字間のスペースは1個残す関数です。
B4〜B6の前後に半角スペースがある文字列には有効であるのが分かります。 - Clean関数は編集記号など表示できない文字を削除することができる関数です。
ここの例では、効果があったのはセル内改行に使われるChar(10)に対してでした。 - Substitute関数は文字列を置き換える関数ですので、=SUBSTITUTE(B3," ","") として半角スペースを取り除くことができました。
- 問題はChar(160) が含まれているものです。
これに関しては、F列に示したように、=SUBSTITUTE(B3,CHAR(160),"")として取り除くことができました。
- Trim関数は前後のスペースを取り除き、文字間のスペースは1個残す関数です。
- では、これらをまとめて処理するには?
- 以上の結果から、Clean関数とSunstitute関数を組み合わせることで処理ができそうです。
G列の数式は =CLEAN(SUBSTITUTE(SUBSTITUTE(B3,CHAR(160),"")," ","")) としました。 - 計算結果は文字列になっているので、数値にしたい・・・といった場合は、後ろに *1 としてください。
=CLEAN(SUBSTITUTE(SUBSTITUTE(B3,CHAR(160),"")," ",""))*1
- 以上の結果から、Clean関数とSunstitute関数を組み合わせることで処理ができそうです。
- 先頭にだけ何か訳が分からないのがあるのなら・・・2文字目から取り出すってことでも対処は可能です。
- =MID(B2,2,LEN(B2)) といった感じです。
- =MID(B2,2,LEN(B2)) といった感じです。
置換機能で不要なスペースを削除する topへ
- セルの値そのものを置き換えたいときには置換を利用します。
- ここでは、下図のD列のスペースを削除します。
(D列は ="A"&B3&"B" として求めたC列をコピーして、D列へ値の貼り付けを行っています。
説明上、スペースが見えるように123の前後にAとBを表示し、C列は非表示にしています。
- D3:D12セルを選択して、[ホーム]タブの[検索と選択]→[置換]を実行します。
- 検索する文字列に 半角スペースを入力します。
[次を検索]と[置換]ボタンを使って半角スペースを削除します。
- 半角スペースが削除できました。
- 今度はCHAR(160)を削除します。
D3:D12セルを選択して、[ホーム]タブの[検索と選択]→[置換]を実行します。
どこかのセル（ここではD14セル) に =CHAR(160) と入力して、そのセルをコピーします。
検索する文字列を選択して、貼り付けます。- コピーは[Ctrl]+[C]、貼り付けは[Ctrl]+[V]で行うと便利です。
- CHAR(160)のスペース文字が削除できました。
- 今度はCHAR(10)を削除します。
D3:D12セルを選択して、[ホーム]タブの[検索と選択]→[置換]を実行します。
セル内改行CHAR(10)は 検索する文字列の欄で [Ctrl]+[J]キーを押します。
見た目には何も変化がありません。[次を検索]と[置換]ボタンを使ってセル内改行を削除します。
- セル内改行CHAR(10)が削除できました。
- ここでは、下図のD列のスペースを削除します。