エクセルの置き換えを関数で一発に!REPLACE関数とSUBSTITUDE


最近はメイン事業も軌道に乗って安定してきた半面、新しい事にチャレンジする機会が減ってきていると感じる今日この頃。

今回はちょっとしたことですが、エクセルでセルの置き換えを関数で一発で終わらせる方法を今更知ったので、次回の自分用に( ..)φメモメモ

エクセルの置き換えする関数には『REPLACE関数』と『SUBSTITUTE関数』の2種類!使い分け方法

今回は以下の写真のように、エクセルで列の数字をSUM関数で合計しようとしたところ、文字列で合計できなかったのが事の発端です。

エクセルの文字列が含まれて合算できない問題が勃発

もう完全に「Pt」が邪魔で数字として認識されない(文字列)ので合計できませんね。これは。手動でやろうにも三桁セルあるので面倒… ここは置き換え関数ってやつを学ぶいいチャンスかもしれない💡

そもそもエクセルの置き換え関数にREPLACE関数があるという事は何となく知っていましたが、使ったことが無かったので調べてみると、REPLACE関数は、指定セルの「指定番目の文字を置き換える」という関数でした。

今回は後ろの「Pt」を「(入力なし)」に置き換えて数字だけにしたかったのですが、文字列を別の文字列で置き換えたい場合には、REPLACE関数ではなくSUBSTITUDE関数を使うのが良さそうです。

【エクセルの置き換え関数2種類の違い】

REPLACE関数(置き換えたいセルの場所、開始位置=何文字目から、文字数=何文字を、置き換え文字列=この文字に置き換える)
⇒●文字目から■文字を▲という文字列に置き換え
 
SUBSTITUTE関数(置き換えたいセルの場所、置き換えたい文字列、置き換え後の文字列)
⇒●という文字列を、■という文字列に置き換え

つまり「何文字目」という決まった番目を置き換えたいならREPLACE関数。どこだか分からないけど、指定の文字列を別の文字列に置き換えたい場合はSUBSTITUTE関数。という使い分けをすれば良さそうです。

実際にSUBSTITUTE関数を使って文字列から数字部分に置き換えの実例

今回の場合は、各セルの「Pt」が邪魔なので、SUBSTITUTE関数で「Pt」を「(なし)」に置き換えして消してみました。実際の関数はこんな感じ↓

SUBSTITUTE関数で置き換える実例 SUBSTITUTE関数で置き換える実例_無事Ptを除去できた

これで「Pt」だけ消えて数字部分を全セル抜き出せたのですが、このままだとエクセル上では「文字列」扱いなので計算できません(数字が表示されていますが「文字列扱い」なので、合計しても0になりますね…)

そこで文字列を数値に置き換えるValue関数を使いましょう!Value関数はそのまま文字列を数値に変換したいセルを選択するだけのお手軽関数です↓

Value関数で文字列から数値に変換すればOK

無事、数字として合算する事ができました↓

Value関数で文字列から数値に変換すればOK_問題なく合算もできた

セルが少なければ手動でいいんですが、今回はかなり多かったので置き換え関数を調べて使ってみました。うーん、日進月歩ですね。