Excelの数字が文字列になって計算・検索できないときの直し方
見た目は同じ数字なのに、SUMで合計できない・検索で見つからない。その多くは、数字が「文字列」として入っていることが原因です。見分け方と、取り込み・全角・見えない空白・末尾マイナスなど原因別の直し方をまとめます。
目次(10項目)
💡 こんな症状はありませんか
- 数字が並んでいるのに、SUMの合計が0や、一部だけの合計になる
- 見た目は同じ番号なのに、VLOOKUPやXLOOKUPが #N/A になる
- 数字がセルの左に寄っている、セルの左上に緑の三角が出ている
- 並べ替えると「1, 10, 100, 2, 20」のような順番になる
これらは、数字が「文字列」として入っているのが原因です。見た目は同じでも、Excelにとって「123」(数値)と「"123"」(文字列)は別のものです。この記事では、原因別に直し方を整理します。
まず確かめる:数値か文字列か
| 方法 | 数値なら | 文字列なら |
|---|---|---|
| セルの配置(標準のまま) | 右寄せ | 左寄せ |
=ISNUMBER(A2) | TRUE | FALSE |
=ISTEXT(A2) | FALSE | TRUE |
| 範囲を選んだときのステータスバー | 「合計」が出る | 「データの個数」だけ出る |
列全体を調べるなら、=COUNT(A2:A1000)(数値の個数)と =COUNTA(A2:A1000)(入っているセルの個数)を比べます。差があれば、その分が文字列です。
原因別の直し方
| 原因 | よくある場面 | 直し方 |
|---|---|---|
| 文字列として取り込まれた | システムのCSV・Webからのコピー | 方法1〜3 |
| 表示形式が「文字列」のセルに入力した | 人から受け取ったファイル | 表示形式を戻してから方法1〜3 |
| 先頭にアポストロフィ(')が付いている | 手入力、他のファイルからの貼り付け | 方法1〜3 |
| 全角の数字(123) | 手入力、帳票からのコピー | 方法4(ASC関数) |
| 見えない空白が付いている | Webや基幹システムからのコピー | 方法4(TRIM・SUBSTITUTE) |
| 末尾にマイナス(123-) | 古いシステムの帳票 | 方法3(区切り位置の詳細設定) |
表示形式は「見せ方」の設定です。すでに文字列として入っている値は、表示形式を変えても文字列のままです。下の方法で値そのものを数値に変えます。
方法1:緑の三角から「数値に変換する」
- 文字列の数字が入った範囲を選びます(最初のセルが緑の三角が出ているセルになるように選びます)。
- 表示される「!」のアイコンをクリックし、「数値に変換する」を選びます。
少ない件数なら、これがいちばん手早い方法です。数万行では時間がかかることがあります。
方法2:「形式を選択して貼り付け」で1を掛ける
- 空いているセルに
1と入力し、コピーします。 - 文字列の数字の範囲を選び、Ctrl+Alt+V(形式を選択して貼り付け)を押します。
- 「貼り付け」で「値」、「演算」で「乗算」を選んで「OK」を押します。
1を掛けた結果は数値になるので、文字列がまとめて数値に変わります。終わったら、1を入力したセルは消します。
方法3:「区切り位置」で列ごと変換する
- 変換したい1列を選びます。
- 「データ」タブ →「区切り位置」を押します。
- そのまま「完了」を押します。
列の値が読み直され、数字は数値に変わります。末尾にマイナスが付いた「123-」は、ウィザードの3つ目の画面で「詳細」を押すと「負の数はマイナスを表示する」があり、チェックが入っていれば「-123」になります(Microsoft 365 版 Excel(Windows、バージョン 2610)では、最初からチェックが入っていました)。
「詳細」の項目の名前は、環境により結果が異なる場合があります。
方法4:関数で変換する(元の列を残したいとき)
文字列の数字を数値にします。
同じ結果になります(「--A2」でも可)。
全角の数字「123」を半角にしてから数値にします。
Webからコピーした値に付いている見えない空白(ノーブレークスペース)と、前後の空白を取ってから数値にします。
変換できないもの(「未定」など)は、元の値をそのまま残します。
関数の結果を元の列に戻すときは、コピーして「値の貼り付け」をします。
検索(VLOOKUP・XLOOKUP)が #N/A になるとき
検索する値と、検索される表の列で、片方が数値・片方が文字列だと一致しません。表の側を直すのが基本ですが、表を触れないときは、式の中で型をそろえます。
検索値が文字列、表が数値のとき:検索値を数値にします。
検索値が数値、表が文字列のとき:検索値に「&""」を付けて文字列にします。
なお、COUNTIFやSUMIFは「123」と「"123"」を同じものとして数えるため、COUNTIFでは見つかるのにVLOOKUPでは見つからないということが起きます。これも型の違いを疑うサインです。
数値にしてはいけない「数字」もある
郵便番号・社員番号・電話番号・商品コードなど、先頭の0に意味がある番号や、16桁以上の番号は、数値にすると0が消えたり、末尾の数字が失われたりします。これらは計算に使わないので、文字列のままにして、照合する相手の側も文字列にそろえます(CSVのゼロが消える原因と正しい取り込み方)。
毎回同じデータで起きるならPower Queryで
毎月取り込むデータで毎回この作業をしているなら、Power Queryで取り込み、列のデータ型を「整数」「10進数」「テキスト」に決めておくと、次からは更新するだけで正しい型になります(データ型の変換)。