ここでは、Excel上でスペースを削除する方法について解説します。
- 氏名の空白スペースの削除などに用います。
- 末尾にスペースが複数ある場合、IFで比較したときに同じ値として認識されません。
- スペースが1つではTRUE扱いになりますが、複数のIF(“ABC “=”ABC”)ではFALSEになります。
- 見た目が明らかに等しいにもかかわらずIF比較でTRUEとならない場合は複数のスペースが末尾にある可能性があります。
| Y | U | K | I | C | H | I | F | U | K | U | Z | A | W | A | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ↓ | ||||||||||||||||||
| ① | Y | U | K | I | C | H | I | F | U | K | U | Z | A | W | A | |||
| ② | Y | U | K | I | C | H | I | F | U | K | U | Z | A | W | A | |||
①は不適当な連続する1個を超えるスペースを削除します。英文などに用います。
②はスペースを完全に削除します。
まず、どちらを目的にするかによって、用いる方法は異なります。
スペースを削除する方法
①TRIM関数を用いる
| 余分な空白スペースを削除するExcel関数/文字操作 | ||||||||||||||||||||||||||||||||
|
=TRIM( 文字列 ) 概要 TRIMは2以上の連続した空白(スペース)について1つを残し、残りをすべて削除する。始まりと終わりのスペースも削除されます。 (例) This is a pen. → This is a pen. |
||||||||||||||||||||||||||||||||
|
日本語でも削除されますが、この関数は英文向きのものです。 複数のスペースに加え、始まりと終わりのスペース(あった場合に限る)も削除されます。
|
||||||||||||||||||||||||||||||||
②SUBSTITUTE関数を用いる
| 指定文字列の置き換えExcel関数/文字操作 | ||
|
=SUBSTITUTE( 文字列 , 検索文字列 , 置換文字列 , (対象) ) 概要 指定した文字列を置き換える |
||
|
=SUBSTITUTE(目的セル,” “,””) とします。このようにすることで、スペースを削除することができます。 =SUBSTITUTE(目的セル,” “,” “)とすることで、TRIMに近い動きをさせることはできますが、この方法では前後のスペースは削除されません。 |
②置換を用いる
関数を用いず、置換によってスペースを削除します。
- 置換したい対象のセルを選択します。
- [ホームタブ]に移動
- [編集]の[検索と選択]グループから置換をクリック(ここまでは、ctrl+Fのショートカットでも起動できます)
- 検索する文字列欄に、スペースを1回入力(スペースキーを押す)します。
- 置換後の文字列欄は何も入力しません。
- すべて置換をクリックします。
※予期しない変換、やり直したいときは、ctrl+Zを押します。
TRIM、SUBSTITUTE、置換の比較
目的に応じてどの方法を使うかを選択してください。
| TRIM |
削除対象は2以上の複数のスペース、セル内最初と最後にスペースがあった場合におけるそのスペース →適切な個数のスペースになります |
|---|---|
| SUBSTITUTE |
全てのスペース →完全にすべて削除されるため、予期しない削除が起きるかもしれません。 |
| 置換 |
全てのスペース →SUBSTITUTEと同様です。 置換の場合はセルそのものが変換されるため、関数の様に元のセルが残るわけではありません。 |
特定の位置のスペースを削除する
先頭のスペースのみを削除する
いくつかの方法が考えられますが、例えば以下の手順で削除をします
- 先頭の文字を取得する(LEFT関数を用いる)
- 先頭の文字がスペースであるかを判断する(IF関数を用いる)
- スペースであったときに、2文字目以降を表示する(MID関数を用いる)
=IF(LEFT(A1,1)=” “,MID(A1,2,9999),A1)
LEFT(A1,1)でA1セルの1文字目を取得します
MID(A1,2,9999)でA1セルの2文字目から9999文字目までを表示します。
3つ目の引数は9999である必要はありませんが、念のため大きな数を入れておきます。
末尾のスペースのみを削除する
いくつかの方法が考えられますが、例えば以下の手順で削除をします
- 末尾の文字を取得する(RIGHT関数を用いる)
- 末尾の文字がスペースであるかを判断する(IF関数を用いる)
- 全体の文字数をカウントする(LEN関数を用いる)
- スペースであったときに、全体の文字数-1までを表示する(LEFT関数を用いる)
=IF(RIGHT(A1,1)=” “,LEFT(A1,LEN(A1)-1),A1)
RIGHT(A1,1)でA1セルの末尾の文字を取得します
LEN(A1)でA1セルの文字数を取得し、-1をすることで末尾スペースを除いた数を取得します。
LEFT(A1,LEN(A1)-1)とすることで、末尾のスペースを除いた文字列が表示されます。
スペース削除に関連する参考解説
Excelの関数を検索する
| 分類 | 関数 | 説明 |
|---|---|---|
| 日付 | DATE | 年月日から日付を求める |
| 日付 | DATEDIF | 開始日から終了日までの期間を求める |
| 日付 | DAY | 日付から日を抽出する |
| 日付 | EDATE | 指定月後の月日を求める |
| 日付 | EOMONTH | 指定月後の月末を求める |
| 日付 | EOMONTH+1 | 指定月後の月初めを求める |
| 日付 | HOUR | 時刻から時間を取り出す |
| 日付 | MINUTE | 時刻から分を取り出す |
| 日付 | MONTH | 日付から付きを抽出する |
| 日付 | NETWORKDAYS | 営業日数を求める(土日休日型) |
| 日付 | NETWORKDAYS.INTL | 営業日数を求める(定休日と休業日指定型) |
| 日付 | NOW | 今日の日付と時刻を求める |
| 日付 | SECOND | 時刻から秒を取り出す |
| 日付 | TIME | 時間を指定して時刻データを作成する |
| 日付 | TODAY | 今日の日付を求める |
| 日付 | WEEKNUM | 1月1日から何週目かを求める |
| 日付 | WEEKDAY | 年月日から曜日番号を求める |
| 日付 | WORKDAY | 営業日について指定した日数後の年月日を求める |
| 日付 | WORKDAY.INTL | 営業日について指定した日数後の年月日を求める(定休日と休業日指定型) |
| 日付 | YEAR | 日付から年を抽出する |
| 文字操作 | & | 文字列を結合する |
| 文字操作 | ASC | 全角文字を半角文字に置き換える |
| 文字操作 | CHAR/CLEAN | 改行を削除する |
| 文字操作 | CONCAT | 文字を範囲指定で結合する |
| 文字操作 | CONCATENATE | 文字列を結合する |
| 文字操作 | COLUMN | 列番号を取得する |
| 文字操作 | DELTA | 数値が等しいかを判定する |
| 文字操作 | EXACT | 文字列が等しいかを判定する |
| 文字操作 | FIND(B) | セル内の指定した文字の位置を求める |
| 文字操作 | ISODD | 指定した数値が奇数か偶数かを判定する |
| 文字操作 | JIS | 半角文字を全角文字に置き換える |
| 文字操作 | LEFT(B) | 指定した文字数分の文字を先頭から取り出す |
| 文字操作 | LEN(B) | 文字数をカウントする |
| 文字操作 | LOWER | 文字列を小文字にする |
| 文字操作 | MID(B) | 指定した文字以降の文字を取り出す |
| 文字操作 | NUMBERSTRING | 漢数字に変換する |
| 文字操作 | PHONETIC | フリガナを取り出す |
| 文字操作 | PROPER | 文字列の先頭を大文字にする |
| 文字操作 | REPLACE(B) | 指定した位置にある文字を置き換える |
| 文字操作 | REPT | 指定回数分の文字列を表示 |
| 文字操作 | RIGHT(B) | 指定した文字数分の文字を末尾から取り出す |
| 文字操作 | ROW | 行番号を取得する |
| 文字操作 | SUBSTITUTE | 指定した文字列を置き換える |
| 文字操作 | SEARCH(B) | セル内の指定した文字の位置を求める |
| 文字操作 | TEXT | 日付などの数値(シリアル値)を文字形式に変換、抽出する |
| 文字操作 | TEXTJOIN | 文字列を結合する |
| 文字操作 | TRIM | 余分な空白を削除する |
| 文字操作 | UPPER | 文字列を大文字にする |
| 文字操作 | VALUE | 文字列を数値(シリアル値など)に変換する |
| 論理 | 論理式 | TRUEとFALSE |
| 論理 | AND | すべての条件を満たす論理式 |
| 論理 | IF | 論理式の結果によって表示を切り替える |
| 論理 | IFS | 複数の論理式の結果によって表示を切り替える |
| 論理 | OR | いずれかの条件を満たす論理式 |
| 論理 | SWITCH | 論理式の結果によって表示を切り替える |
| 数学 | AGGREGATE | 指定した19の集計方法で集計値を求める |
| 数学 | CEILING | 基準値の単位で切り上げる |
| 数学 | FLOOR | 基準値の単位で切り下げる |
| 数学 | INT | 小数点以下を切り捨てる |
| 数学 | ISEVEN | 偶数柿数かを判定する |
| 数学 | MOD | 割り算の余りを求める |
| 数学 | MROUND | 基準値の単位で四捨五入する |
| 数学 | POWER | べき乗を求める |
| 数学 | PRODUCT | 掛け算を求める |
| 数学 | QUOTIENT | 割り算の商を求める |
| 数学 | RAND | 乱数を発生させる |
| 数学 | ROUND | 指定した桁で四捨五入する |
| 数学 | ROUNDDOWN | 指定した桁で切り捨てる |
| 数学 | ROUNDUP | 指定した桁で切り上げる |
| 数学 | SUBTOTAL | 指定した11の集計方法で集計値を求める |
| 数学 | SUM | 数値の合計を求める |
| 数学 | SUMIF | 特定の条件を満たすセルの合計値を求める |
| 数学 | SUMIFS | 複数の条件を満たすセルの合計値を求める |
| 数学 | SUMPRODUCT | 条件付きセルの乗算数の合計を求める |
| 数学 | SUMPRODUCT | セルの乗算数の合計を求める |
| 数学 | TRUNC | 指定した桁数を切り捨てる |
| 数学 | TRUNC | 小数点以下を切り捨てる |
| 検索 | CHOOSE | インデックス数値に対応する値を表示する |
| 検索 | FILTER | 条件に一致する行のデータを求める |
| 検索 | INDEX | 範囲内の指定したデータを取り出す |
| 検索 | INDEX | 指定した行位置に対応する列データを取り出す |
| 検索 | INDEX | 指定した列位置に対応する行データを取り出す |
| 検索 | LOOKUP | 検索値に一致するデータを検索する |
| 検索 | MATCH | 値に一致する行位置を求める |
| 検索 | MATCH | 値に一致する列位置を求める |
| 検索 | UNIQUE | 重複するデータをまとめる |
| 検索 | VLOOKUP | 検索値に一致するデータを検索する |
| 検索 | VLOOKUP | 検索値に対応するデータ(範囲型)を求める |
| 統計 | AVERAGE | 平均値を求める |
| 統計 | AVERAGEA | 平均値を求める |
| 統計 | AVERAGEIF | 条件に合う数値の平均値を求める |
| 統計 | AVERAGEIFS | 複数の条件に合う数値の平均値を求める |
| 統計 | COUNT | 指定したデータの個数を求める |
| 統計 | COUNTA | 指定したデータの個数を求める |
| 統計 | COUNTBLANK | 空白セルの個数を求める |
| 統計 | COUNTIF | 条件に合うデータ数を求める |
| 統計 | COUNTIFS | 複数の条件に合うデータ数を求める |
| 統計 | FREQUENCY | データの度数を求める |
| 統計 | LARGE | 大きいほうからの順位の数値を求める |
| 統計 | MAX | データの最大値を求める |
| 統計 | MAXA | データの最大値を求める |
| 統計 | MAXIFS | 複数条件を満たす最大値を求める |
| 統計 | MEDIAN | 中央値を求める |
| 統計 | MIN | データの最大値を求める |
| 統計 | MINA | データの最大値を求める |
| 統計 | MINIFS | 複数条件を満たす最小値を求める |
| 統計 | MODE.MULT | データの最頻値を求める(こちらを推奨) |
| 統計 | MODE.SNGL | データの最頻値を求める(MODE.MULT推奨) |
| 統計 | PERCENTILE.EXC | 百分位数(パーセント)を求める |
| 統計 | PERCENTILE.INC | 百分位数(パーセント)を求める |
| 統計 | PERCENTRANK.EXC | データの位置を百分率で求める |
| 統計 | PERCENTRANK.INC | データの位置を百分率で求める |
| 統計 | QUARTILE.EXC | 四分位数を求める |
| 統計 | QUARTILE.INC | 四分位数を求める |
| 統計 | RANK.EQ | データの順位を求める |
| 統計 | SMALL | 小さいほうからの順位の数値を求める |
| 統計 | STDEV.P | 標準偏差(母集団型)を求める |
| 統計 | STDEV.S | 標準偏差(標本型)を求める |
| 統計 | STDEVA | 標準偏差(標本型で論理等含む)を求める |
| 統計 | STDEVPA | 標準偏差(母集団型で論理等含む)を求める |
| 統計 | VAR.P | データの分散(標本分散)を求める |
| 統計 | VAR.S | データの分散(不偏分散)を求める |
| 統計 | VARA | データの分散(不偏分散)を求める |
| 統計 | VARPA | データの分散(標本分散)を求める |