今回の記事では
指定した文字より後ろの文字を関数を使って取り出すにはどうしたら良いの？
具体的な手順や設定方法を教えてほしい
指定した文字より前の文字を関数で取り出す方法ってあるの？
何か特別な設定方法や工夫が必要ならその方法も分かっておきたい
こういった疑問に答えます
✔指定位置より前後の文字の取り出し方のテーマ
①指定文字より後ろの文字はMID関数やRIGHT+LEN関数を使えば簡単に取り出せます
②指定文字より前の文字はMID関数やLEFT+LEN関数を使えば簡単に取り出せます
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「指定位置より前後の文字の取り出し方」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
指定文字より後ろの文字はMID関数やRIGHT+LEN関数を使えば簡単に取り出せます
指定した文字より後ろの文字は各関数を組み合わせれば意外と簡単に取得出来ます
なぜなら、各関数の結果を上手く組み合わせることでやりたいことを実現出来るからです
では具体的な例を基に設定方法を見ていきましょう
こんな風に「商品管理コード」から指定した文字をとってくる方法です
今回は「25」で始まるコードなので「25以降の数字」を表示することが目的です
1つ目のやり方が「取得する文字数を決め打ちで指定」する方法ですが、「決め打ち」とはあらかじめ決まったやり方で実行することです
つまり、商品コードは全部で「13文字」あり「25」以降の文字数は「13文字-2文字=11文字」ですよね
この「11」という文字を固定(決め打ち)して25以降の文字を取得するという訳です
文字だけだと分かりづらいので実際に関数を使った取得方法を見ていきましょう
設定内容は「=MID(A5,3,11)」で「=MID(文字列,開始位置,文字数)」と指定します
MID(ミッド)関数は「指定した範囲の文字列を取得」してくれる関数です
分かりやすいように日本語にすると「=MID(A5のセルの文字を,3文字目から,11文字目まで表示してね)」という意味です
図解するとこんな感じです
この方法なら簡単に25以降の文字を取得することが出来ます
ただし、決め打ちをしているのでもし商品管理コードのルールが変わって長くなったり短くなった時は設定を全て変える必要があります
その対策方法で一番簡単な方法が「取得する文字数を最初から大きくしておく」方法です
やり方はすごく簡単です
この方法は凄くシンプルです
式は「=MID(A5,3,1000)」さっきと同じでMID関数を使って「3文字目～1,000文字目」までを取得しています
1,000文字ならほぼ表示できるという、いわゆる荒業になります
ただ、決め打ちでも取得する文字数を増やしてもあまりスマートとは言えないですね
そんな時は、「RIGHT関数とLEN関数」を組み合わせる(ネストする)ことで今後コードが伸びても縮んでも融通がきく方法を見てみましょう
それがこの方法です
「=RIGHT(A5,LEN(A5)-2)」こんな風にRIGHT関数とLEN関数をネストすれば同じことが出来ます
RIGHT(ライト)関数は「RIGHT(文字列,文字数)」と設定して文字の右端から○文字目までを表示する関数です
LEN(レン)関数は「LEN(文字列)」と指定して指定したセルに文字列が全部で何文字なのか数えて表示してくれる関数です
今回の式を日本語にすると「=RIGHT(A5のセル内の文字列を右から,LEN関数の結果-2文字分表示して)」という意味になります
今回のポイントは「LEN関数の結果-2」の部分でLEN関数の結果は「13」です
「13-2=11」は今回除外したい「25を商品管理コードから抜いた数字と一緒」ですよね
これは一番最初に説明したやり方と一緒です
その11文字分をRIGHT関数で抜き出してくると、同じ結果を表示することが出来ます
文章だけでは分かりづらいので図解してみましょう
こんな感じで各関数の結果を式にして活用することで目的を達成出来ます
これらの関数の活用方法は「空白で文字を分けるには4つの関数を組合せ」という記事でも紹介しているので、もっと活用したくなったら見てみて下さい
繰り返しになりますが、指定した文字より後ろの文字は各関数を組み合わせれば意外と簡単に取得出来ます
指定文字より前の文字はMID関数やLEFT+LEN関数を使えば簡単に取り出せます
目的に合った関数を組み合わせれば指定文字より前の文字列を取得することは簡単に出来ます
なぜなら、指定文字より前でも後でも基本的なやり方はそこまで変わらないからです
では、実際に指定した文字より前の文字の取得方法について見ていきましょう
今回の場合は下3桁以前の文字列を取得する方法です
設定内容は「=LEFT(A5,10)」でこれはRIGHT関数を使った決め打ちでの取得方法と同じですね
LEFT関数の使い方は、RIGHT関数と同じなので省略します
でも、この方法だと取得する文字数が変わったら、そのたびに変更しなくてはいけないのでスマートではないですよね
そこでMID関数を使って同じ結果をもっとスマートに実現してみましょう
それがこんな方法です
式はかなりシンプルですね
「=MID(A5,1,10)」これだけで終わりです
MID関数は文字列の指定の範囲を取得してくれる関数なので、「=MID(A5のセルの文字列から,1文字目～,10文字目を表示してね)」という意味になります
これで下3桁より前の文字列を取得出来ますね
もう一つのやり方がLEFT関数とLEN関数を組み合わせる方法です
これもやり方を確認してみましょう
やり方は指定文字より前の文字列を取得する方法と基本は一緒です
式は「=LEFT(A5,LEN(A5)-3)」なので違いはRIGHT関数とLEFT関数の違い・マイナスする文字数の違いくらいですね
また、「-3」は他のセルに「3だけ入力」しておき「-A1」のようにセルを参照した計算方法にすれば、修正があった場合でも柔軟に対応出来ますね
こんな風に慣れとアイディアは必要になりますが、意外と簡単な方法で目的を達成することができてしまうので覚えてみると楽しくなりますよ
繰り返しになりますが、目的に合った関数を組み合わせれば指定文字より前の文字列を取得することは簡単に出来ます
よくある質問：指定した位置より前や後の文字列の取得方法は分かったけど、指定した文字を中心に前後の文字を取得する方法は無いですか？
指定した文字を中心に前後の文字列を取得するには、色々な関数を上手に組み合わせれば実現できます
理由は1つの関数だけで目的の機能を実現することは難しいからです
では具体的なやり方を見てみましょう
まずは、指定された文字より後の文字の取得方法です
関数の式の内容は、こんな感じです
「=RIGHT(A5,LEN(A5)-FIND(B5,A5))」基本は今までとあまり変わりませんが、違いはFIND(ファインド)関数です
FIND関数は文字列を対象セルから検索する関数で「=FIND(検索文字列,対象)」と指定します
今回の設定は「FIND(B5,A5)」なのでコレを日本語にすると「FIND(B5のセルの内容を検索してね,A5セルの中で見つけたら位置を教えてね)」
要は「中央区千葉港1-1」の場合「区」は左から3番目なので「3」を返してくれます
そして次のポイントが「LEN(A5)-FIND(B5,A5)」です
LENは文字の総数を表示してくれる関数なので答えは「9」です
ということは「9-3=6」ですね
仕上げにRIGHT関数を使って「A5のセルを右から6文字分取得する」ということになります
図解にするとこんな感じです
それなりに複雑ですが、FIND関数を使って「区」の位置を特定するところがポイントなので、小分けにして少しずつ理解してみましょう
次は区よりも前の文字を取得する方法です
まずは設定内容を見てみましょう
さっきより式は短くなりましたが、大体同じような内容です
この場合のポイントは「FIND(B5,A5)-1」です
結果は「2」なのでLEFT関数を使って文字の頭から2文字を表示しているという訳です
「2」になる理由は「区の位置は3」となりますが「区」は含みたくないので「-1」をすることで調整しているという工夫をしています
こちらも慣れないと難しく感じますが、少しずつで大丈夫なので分解して理解してみましょう
繰り返しになりますが、指定した文字を中心に前後の文字列を取得するには、色々な関数を上手に組み合わせれば実現できます
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す