【Excel】Ctrl + Hで円・kgを取り除く方法|数値化と単位表示の注意点

【Excel】Ctrl + Hで円・kgを取り除く方法|数値化と単位表示の注意点
🛡️ 超解決

Excelの「1,200円」や「2.5kg」が計算できないときは、単位がセルの値に含まれているのか、表示形式で付いているだけなのかを確認します。見た目が同じでも、前者は文字列、後者は計算できる数値である場合があります。確認せずに「円」や「kg」を一括削除するのは避けてください。

【要点】数式バーで値を確認し、文字列に含まれる単位だけを対象範囲内で置換します。数値化できたら、単位は列見出しやユーザー定義書式で表示します。以下はWindows版デスクトップExcelの例です。

ADVERTISEMENT

1. セルの値にある単位か、表示だけの単位かを見分ける

対象セルを選び、数式バーを見ます。セルには「1,200円」と表示されていても、数式バーが1200なら、表示形式で円を付けている可能性があります。この場合は単位を消す置換をする必要がありません。Ctrl + 1の「表示形式」で確認し、表示から円を外したいだけなら数値書式へ変えます。

数式バーにも「1,200円」という文字列がある場合は、単位を含めて入力された値です。数式バーが=で始まる場合は数式なので、置換で数式の一部を変えるのではなく、参照元と結果の作り方を確認します。空きセルの=ISNUMBER(A2)でも、数値かどうかを調べられます。

文字列1,200円から単位を除いて数値1200とし、ユーザー定義書式で1,200円と表示するまでの値と表示の違い
値を数値1200にしたうえで、表示形式だけに円を付ければ計算に使えます。

金額の入力として認識される表記は地域や入力方法にも関係します。「文字が見えるから必ず文字列」「右寄せだから必ず数値」とは判断せず、保存された値を調べるところから始めます。

※ お探しの解決策が見つからない場合は、こちらの「Excelトラブル完全解決データベース」で他のエラー原因や解決策をチェックしてみてください。

2. コピーした金額列でCtrl + Hを実行する

  1. 元ブックを別名で保存します。元の列を残し、修正用の列へ対象データをコピーします。
  2. 例えばB2:B100のように、単位を消すデータ部分だけを選びます。見出しや備考欄は含めません。
  3. Ctrl + Hで「検索と置換」を開きます。
  4. 「検索する文字列」に円、「置換後の文字列」には何も入れず空欄にします。
  5. オプションで検索場所、書式条件、完全一致の設定を確認し、まず1件を置換します。
  6. 意図した文字だけが消えたことを確認してから、同じ範囲へ「すべて置換」を実行します。

単位を消す場合、セル内容の完全一致は通常オフにします。「セル内容が完全に同一であるものを検索する」をオンにすると、検索文字が円なら「円」だけのセルが対象となり、「1200円」には一致しません。備考の「円滑」を守る方法は、完全一致で金額を処理することではなく、金額の範囲だけを選ぶことです。

置換の詳細はMicrosoftの検索と置換の説明を参照できます。非表示やフィルターの状態に頼って処理範囲を決めず、対象を別の作業列・シートへ分けて明示すると確認しやすくなります。

削除したい記号が*や?の場合は、検索で特別な意味を持つワイルドカードに注意します。文字としての*を探すなら~*、?なら~?と入力します。円やkgの置換条件をそのまま別の記号へ使い回さず、検索で実際に何が選ばれるかを先に確認してください。

3. 単位を消した後に数値型を確認する

置換後の「1200」が必ず数値になったとは限りません。空きセルでISNUMBERを使い、FALSEならまだ数値以外です。計算すべき金額であることが確認できたら、隣の列でVALUEを使います。元の列を残して変換すると、置換と数値化のどちらで問題が起きたかを調べられます。

=VALUE(B2)

置換の代わりに、決まった単位だけを除く式で比較することもできます。A2に「2.5kg」という文字列がある場合、次の式はkgを除いた文字列を数値へ変換します。ここでは小数点がピリオドとして認識される環境を前提にしています。

=VALUE(SUBSTITUTE(A2,"kg",""))

結果は2.5です。「3kg」と合わせた合計は5.5になります。SUBSTITUTEは一致する文字を置き換える関数なので、Kg、Kg、㎏のような別表記を同じものとして自動処理するわけではありません。数値化の条件はVALUE関数の説明も確認します。

gとkg、円と千円が混在する列では、単位を消すだけでは合計できません。例えば500gは0.5kgです。元の単位を別列へ残し、統一する単位と換算方法を決めてから数値化してください。

4. カンマや空白をまとめて消さない

「1,200円」のカンマは桁区切りですが、海外のデータの「1,5kg」では小数点の役割を持つ場合があります。カンマを一律に消せば、1.5という意味の値を15へ変えてしまいます。数字の表記ルールを確認してから処理します。

小数点をカンマ、桁区切りをピリオドとするデータなら、NUMBERVALUEでそれぞれを指定できます。例えば単位を取り除いた文字列「1,5」に対し、次の式は1.5を表す数値を返します。

=NUMBERVALUE("1,5",",",".")

空白も同様です。前後の半角スペースを整理するTRIMは便利ですが、すべての全角スペースや特殊な空白を消すわけではありません。負数の符号、小数点、範囲を表す「~」が混じるセルも、見た目だけで削除しないでください。「約10kg」や「10~12kg」は単一の確定した重量ではないため、そのまま数値へ決めつけず別扱いにします。

5. 計算できる値へ単位を表示する

数値にした後も単位を見せたい場合は、列見出しを「金額(円)」「重量(kg)」にする方法が簡単です。セルごとに単位を出すなら、対象の数値セルを選んでCtrl + 1を押し、「表示形式」の「ユーザー定義」へ進みます。

#,##0"円"

この書式なら、数値1200を1,200円と表示できます。小数1桁を使う重量なら次のように指定します。

0.0"kg"

数値2.5は2.5kgと表示されますが、保存された値は2.5のままです。これはgをkgへ換算する設定ではないため、500という値に付ければ500kgと表示されます。単位の表示と換算を混同しないでください。

Microsoftのユーザー定義書式の手順では、付け足す文字を引用符で囲む方法が案内されています。Web版ではユーザー定義書式の新規作成ができないため、必要ならデスクトップ版で設定します。

6. 誤置換の取り消しと対象外セルの点検

意図しないセルが変わった場合は、作業を続けずCtrl + Zで置換を取り消します。元へ戻せたことを確認してから対象範囲を狭めます。保存して閉じた後など、取り消しができない場合は元ブックや原データから復元します。

「円」が見つからないときは表示形式だけの円ではないか、検索の書式条件が残っていないか、完全一致がオンではないかを確認します。「kg」が消えないセルは文字種や大文字・小文字を比較します。問題のあるセルを0に置き換えて合計を合わせるのは避けてください。

数式セルに置換をかけると、参照先ではなく式中の文字を変える場合があります。修正対象が入力値なのか数式なのかを分け、商品名・住所・注記などの文章列は対象から外します。マイナスの金額が残っていることや、空欄が勝手に0へ置き換わっていないことも確認します。

7. 単位と合計の意味が合うところまで確認する

最後に、代表的な数行を原データと照合し、件数、数値型、符号、小数、単位、合計を確認します。数字だけになったことと正しく計算できることは同じではありません。換算が必要な行を見落としていないかも確かめます。

今後の入力は、値を数値、単位を列見出しや表示形式に分けると再利用しやすくなります。既存データを直す際も、まず値と表示の違いを見分け、必要な範囲だけを処理することが大切です。

📊
Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。
この記事の監修者
📈

超解決 Excel・Word研究班

企業のDX支援や業務効率化を専門とする技術者チーム。20年以上のExcel・Word運用改善実績に基づき、不具合の根本原因と最短の解決策を監修しています。ExcelとWordを使った「やりたいこと」「困っていること」「より便利な使い方」をクライアントの視点で丁寧に提供します。

🏆
超解決 Excel検定 あなたのExcel実務能力を3分で測定!【1級・2級・3級】