Excelの数字が文字列になって計算・検索できないときの直し方

見た目は同じ数字なのに、SUMで合計できない・検索で見つからない。その多くは、数字が「文字列」として入っていることが原因です。見分け方と、取り込み・全角・見えない空白・末尾マイナスなど原因別の直し方をまとめます。

目次(10項目)

💡 こんな症状はありませんか

  • 数字が並んでいるのに、SUMの合計が0や、一部だけの合計になる
  • 見た目は同じ番号なのに、VLOOKUPやXLOOKUPが #N/A になる
  • 数字がセルの左に寄っている、セルの左上に緑の三角が出ている
  • 並べ替えると「1, 10, 100, 2, 20」のような順番になる

これらは、数字が「文字列」として入っているのが原因です。見た目は同じでも、Excelにとって「123」(数値)と「"123"」(文字列)は別のものです。この記事では、原因別に直し方を整理します。

まず確かめる:数値か文字列か

見分け方
方法数値なら文字列なら
セルの配置(標準のまま)右寄せ左寄せ
=ISNUMBER(A2)TRUEFALSE
=ISTEXT(A2)FALSETRUE
範囲を選んだときのステータスバー「合計」が出る「データの個数」だけ出る

列全体を調べるなら、=COUNT(A2:A1000)(数値の個数)と =COUNTA(A2:A1000)(入っているセルの個数)を比べます。差があれば、その分が文字列です。

原因別の直し方

原因と直し方の早見表
原因よくある場面直し方
文字列として取り込まれたシステムのCSV・Webからのコピー方法1〜3
表示形式が「文字列」のセルに入力した人から受け取ったファイル表示形式を戻してから方法1〜3
先頭にアポストロフィ(')が付いている手入力、他のファイルからの貼り付け方法1〜3
全角の数字(123)手入力、帳票からのコピー方法4(ASC関数)
見えない空白が付いているWebや基幹システムからのコピー方法4(TRIM・SUBSTITUTE)
末尾にマイナス(123-)古いシステムの帳票方法3(区切り位置の詳細設定)
表示形式を「数値」に変えるだけでは直りません。
表示形式は「見せ方」の設定です。すでに文字列として入っている値は、表示形式を変えても文字列のままです。下の方法で値そのものを数値に変えます。

方法1:緑の三角から「数値に変換する」

  1. 文字列の数字が入った範囲を選びます(最初のセルが緑の三角が出ているセルになるように選びます)。
  2. 表示される「!」のアイコンをクリックし、「数値に変換する」を選びます。

少ない件数なら、これがいちばん手早い方法です。数万行では時間がかかることがあります。

方法2:「形式を選択して貼り付け」で1を掛ける

  1. 空いているセルに 1 と入力し、コピーします。
  2. 文字列の数字の範囲を選び、Ctrl+Alt+V(形式を選択して貼り付け)を押します。
  3. 「貼り付け」で「値」、「演算」で「乗算」を選んで「OK」を押します。

1を掛けた結果は数値になるので、文字列がまとめて数値に変わります。終わったら、1を入力したセルは消します。

方法3:「区切り位置」で列ごと変換する

  1. 変換したい1列を選びます。
  2. 「データ」タブ →「区切り位置」を押します。
  3. そのまま「完了」を押します。

列の値が読み直され、数字は数値に変わります。末尾にマイナスが付いた「123-」は、ウィザードの3つ目の画面で「詳細」を押すと「負の数はマイナスを表示する」があり、チェックが入っていれば「-123」になります(Microsoft 365 版 Excel(Windows、バージョン 2610)では、最初からチェックが入っていました)。

「詳細」の項目の名前は、環境により結果が異なる場合があります。

方法4:関数で変換する(元の列を残したいとき)

=VALUE(A2)

文字列の数字を数値にします。

=A2*1

同じ結果になります(「--A2」でも可)。

=VALUE(ASC(A2))

全角の数字「123」を半角にしてから数値にします。

=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),"")))

Webからコピーした値に付いている見えない空白(ノーブレークスペース)と、前後の空白を取ってから数値にします。

=IFERROR(VALUE(ASC(TRIM(A2))),A2)

変換できないもの(「未定」など)は、元の値をそのまま残します。

関数の結果を元の列に戻すときは、コピーして「値の貼り付け」をします。

検索(VLOOKUP・XLOOKUP)が #N/A になるとき

検索する値と、検索される表の列で、片方が数値・片方が文字列だと一致しません。表の側を直すのが基本ですが、表を触れないときは、式の中で型をそろえます。

=XLOOKUP(VALUE(A2),マスタ!A:A,マスタ!B:B,"該当なし")

検索値が文字列、表が数値のとき:検索値を数値にします。

=XLOOKUP(A2&"",マスタ!A:A,マスタ!B:B,"該当なし")

検索値が数値、表が文字列のとき:検索値に「&""」を付けて文字列にします。

なお、COUNTIFやSUMIFは「123」と「"123"」を同じものとして数えるため、COUNTIFでは見つかるのにVLOOKUPでは見つからないということが起きます。これも型の違いを疑うサインです。

数値にしてはいけない「数字」もある

郵便番号・社員番号・電話番号・商品コードなど、先頭の0に意味がある番号や、16桁以上の番号は、数値にすると0が消えたり、末尾の数字が失われたりします。これらは計算に使わないので、文字列のままにして、照合する相手の側も文字列にそろえます(CSVのゼロが消える原因と正しい取り込み方)。

目安:足したり平均したりする数字 → 数値にする/番号として見る数字 → 文字列のまま

毎回同じデータで起きるならPower Queryで

毎月取り込むデータで毎回この作業をしているなら、Power Queryで取り込み、列のデータ型を「整数」「10進数」「テキスト」に決めておくと、次からは更新するだけで正しい型になります(データ型の変換)。