今回の記事では
同じ記号を使った文字列の置換をしたいけど上手く置換することが出来ない
どうしたら目的の文字列を置換すれば良いのか教えてほしい
全半角の空白が混ざった文字列から一気に空白を削除する方法って無いの？
なるべく簡単な方法を知りたいけどそんなやり方ってある？
こういった疑問に答えます
✔置換が上手く出来ない時の正しい関数の使い方のテーマ
①同じ記号を含む文字列でもSUBSTITUTE関数を使えばちゃんと置換出来ます
②全半角が混ざった空白の置換ならSUBSTITUTE関数の組み合わせで実現可能
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「置換が上手く出来ない時の正しい関数の使い方のテーマ」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
同じ記号を含む文字列でもSUBSTITUTE関数を使えばちゃんと置換出来ます
同じ記号が複数含まれる文字列を上手く置換したいならSUBSTITUTE関数を使うのが最適解です
なぜなら、SUBSTITUTE関数を使えば元の文字を残しつつ目的の位置の記号だけ置換することが出来るからです
今回は「-(ハイフン)」が2つ含まれた型番から任意の位置の「-」を削除する方法について見ていきましょう
こんな感じで2番目の「-」を削除してみましょう
設定方法はすごく簡単なのでチャレンジしてみましょう
設定内容は「=SUBSTITUTE(A5,”-“,””,2)」です
SUBSTITUTE関数の基本的な使い方は「=SUBSTITUTE(文字列,検索文字列,置換文字列,［置換対象］)」です
今回の数式を見てみましょう「=SUBSTITUTE(A5,”-“,””,2)」この数式を日本語にすると「=SUBSTITUTE(A5セルの文字列の,「-」を見つけて,「-」があったら空白に置換してね,ただし「2」個目の「-」を置換してね)」という意味になります
今回のポイントは2つで「-」を見つけたら「空白」に置換するということと、2個目の「-」を置換してねという部分です
まず、「-」を「空白」するというのは「空白=削除」と同じ意味になります
空白は「”(ダブルクォーテーション)」を２つ連続で入力するだけです
これで目的の記号を削除することが出来ます
但し今回は2番目の「-」を削除したいので「置換対象の引数」に「2」を指定しました
動きを図解にするとこんな感じです
こんな風に2つ目の「-」を削除してねという意味になるので狙った位置の記号を削除できちゃいます
関数を使った置換については「文字列置換ならSUBSTITUTEとREPLACE関数」でも紹介しているので、もっと詳しく知りたい時は参考にしてみて下さい
繰り返しになりますが、同じ記号が複数含まれる文字列を上手く置換したいならSUBSTITUTE関数を使うのが最適解です
よくある質問：置換と言えば置換機能があるけど、その方法の方が早い気がするけどそれじゃだめなの？
置換機能を使った方が簡単に置換する事ができますが、狙った場所の置換をすることは出来ません
なぜなら、置換機能は同じ文字列などがあると自動で判断することが出来ないからです
それでは置換機能を使って実際に「-」を削除してみましょう
先に置換したい範囲を範囲選択しておきます
置換機能を使うと元の文字列を上書きすることになるので、他の部分に影響がないように範囲選択しておくのが正解です
「Ctrlキー+Hキー」を押して「検索と置換ダイアログ」をショートカットキーで呼び出します
「検索する文字列」に「-」置換後の文字列に「空白」を入力
「置換」ボタンをクリック
その結果はこんな風に全ての「-」が削除されてしまいます
つまり、置換機能を使った場合は1個目の記号はそのままでということが出来ないのでこんな時はSUBSTITUTE関数を使うのがベストだということです
また、置換機能の場合元の値を上書きしてしまうので目的に合わせて置換機能とSUBSTITUTE関数を使い分けましょう
置換機能を使った方が簡単に置換する事ができますが、狙った場所の置換をすることは出来ません
全半角が混ざった空白の置換ならSUBSTITUTE関数の組み合わせで実現可能
全半角の空白が混ざった文字列を上手く置換するにはSUBSTITUTE関数を組み合わせて使うのが最適解です
なぜなら、全半角の空白はパッと見で判断することがむすかしく手で削除するよりもSUBSTITUTE関数を使った方が圧倒的に簡単だからです
では実際にやり方を解説していきましょう
今回は購入年月日が空白を使ったおかしな表現になっているのでこれを修正していきます
こんな風に年月日が全半角のスペースでまばらに間隔が開いています
この日付を最終的に普通の日付に修正する方法を見ていきましょう
まずは全角スペースを削除する方法を基に基本を押さえましょう
今回の式は「=SUBSTITUTE($B5,“ ”,“/”)」なので日本語にすると「=SUBSTITUTE(B5セルの文字列の,「全角の空白」を見つけてね,見つかったら「/」に置換してね)」
という意味になります
つまり、全角の空白と / を入れ替えることが出来たという訳です
この処理が基本になるので覚えておいてくださいね
結果を見ると全角は問題無く置換されていますが、半角の空白はそのままなのでこの方法を応用して次の式を設定します
こんな風にSUBSTITUTE関数を重ねる(ネスト)すると全角でも半角でも空白を削除することが出来ます
今回の式は「=SUBSTITUTE(SUBSTITUTE($B5,” ”,”/”),” “,“/”)」なのでコレを日本語にすると「=SUBSTITUTE(SUBSTITUTE関数の結果から,「半角のスペース(空白)」を,「/」に置換してね)」という意味になります
さて、この関数はパッと見ると難しそうですがこうするともう少し見やすくなります
「=SUBSTITUTE(1回目の式,” “,”/”)」こんな感じです
どうです？ けっこう見やすくなりましたよね
つまり、一回目の全角の空白を置換した結果を対象に更に半角の空白を置換したという訳です
ちょっと分かりづらいかもしれませんが、こんな風に1回目の結果を基にもう一度同じ関数を使うことで2重に置換したことと同じ結果になることを覚えておきましょう
この方法は、「復数置換ならSUBSTITUTE関数」でも紹介しているのでイマイチよく分からなかったり、練習をもっとしてみたい時に参考にしてみて下さい
最後の仕上げは日付に変換する方法です
設定はすごく簡単なので見てみましょう
これだけでOKです
今回の関数は「=DATE(YEAR(D5),MONTH(D5),DAY(D5))」なのでこの四季を日本語にしてみましょう「=DATE関数で日付に変換してね(年数は「D5セル」から取得,月数も「D5セル」から取得,日数も「D5」セルから取得してね)」
という意味になります
DATE関数は「DATE(年,月,日)」と指定するだけで文字列を日付(シリアル値)に変換してくれます
「YAER・MONTH・DAY」の各関数は対象のセルから年数などの数字を取得してくれる関数です
それぞれの関数を組み合わせて本当の日付に変換しています
ピンとこない場合は、DATE関数は「離れたセルの年月日はDATE関数を使うのが正解」・「YAER・MONTH・DAY」の各関数については「YEAR・MONTH・DAY関数で簡単年月日分け」という記事でも解説しているので参考にしてみて下さい
でも、どうして日付になっているのにもう一度日付に変換してるのか良く分からないですよね
理由は見た目は日付ですが日付(シリアル値)になっていないからです
では、「表示形式を標準」に変更して両方を見比べてみましょう
こんな風に両方の日付を見比べると一目瞭然ですよね
右側はちゃんとシリアル値になていますが、左側は文字列になっています
日付を扱う時はシリアル値が基本になるのでちゃんとした日付にするにはこんな方法が有効です
シリアル値が良く分からない場合は「日付の正体はシリアル値という数字」という記事でも解説しているので参考にしてみてくださいね
繰り返しになりますが、全半角の空白が混ざった文字列を上手く置換するにはSUBSTITUTE関数を組み合わせて使うのが最適解です
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す