LEFT、RIGHT、LEFT関数で文字列の一部を取り出すことができますが、文字数が一定でない場合はどうすればよいでしょうか。
例えば、特定の文字を検索して見つかったらその文字まで取り出して、見つからなかったら空白にするとか、特定の文字間にある文字列を取り出すとか、右から数えて文字を取り出すとか、複数の文字のいずれかの左側を取り出すとか・・・挙げたらきりがありませんが、LEN関数やFIND関数と組み合わせることによって、複雑な文字列の抽出が可能となります。
そこで、今回は、LEN、FIND、LEFT、RIGHT、MIDを組み合わせて文字列を取り出す練習問題を出題します。
目次
- ０．文字列の抽出をするのに必要な関数（復習）
- １．右から数えた文字数の指定
- ２．特定の文字を検索して左側・右側を取得する
- ３．特定の文字が見つからなかった場合の処理
- ４．全体から一部を除いた残りの文字列
- ５．特定の文字の2番目まで取り出す
- ６．2つの文字に囲まれた部分を取り出す
- ７．複数条件の取り出し
０．文字列の抽出をするのに必要な関数（復習）
この記事ではおもに５つの関数を使用します。これらの違いを理解したうえで演習をしましょう。
（１）文字列の取り出し LEFT関数、RIGHT関数、MID関数
LEFT、RIGHT、MID関数は文字列を取り出す関数なので「文字列」が返されます。これらの基本問題についてはこちらの記事をご覧ください。
（２）文字位置の計算 LEN関数、FIND関数
LEN、FIND関数は文字数又は特定の文字の位置をカウントする関数なので「整数値」が返されます。これらの基本問題についてはこちらの記事をご覧ください。
上記の記事で何をやったのか、おさらいをしておきましょう。
- LEN、FINDは全角も半角も1文字としてカウントする
- FINDは左から何文字目かを数える
- FINDで複数文字を検索する場合
- IFERRORでエラーになっているものを強制的に０にする
- 都道府県名の文字数
- 空白セル、空白文字列の関係
- 特定文字列の2番目、3番目を求める
- FIND関数の開始位置（第3引数）の仕様
１．右から数えた文字数の指定
（１）右側のN文字を削除
問題
A列は英字＋数字4文字＋英字1文字である。A列の文字列から数字の左側にあるアルファベットの文字列を取り出しなさい。
解説
文字列の先頭から取り出す関数はLEFT関数です。しかし、取り出す文字数がバラバラです。文字数は、全体の文字数から、数字4文字と最後の英字を除きます。したがってLEN(A1)-5となります。
「=LEFT(A1,LEN(A1)-5)」と入力します。これは後ろの5文字を削除しているのと同じです。
LEFT関数で取得する文字数が一定でない場合で、右側の取得しない文字列の文字数がNであれば、文字数は「LEN-N」とします。
- =LEFT(文字列,LEN(文字列)-N)
（２）右から数えてN文字目からM文字取得
問題
A列の文字列から数字4文字を取り出しなさい。
解説
文字列の途中から取り出す関数はMID関数です。取り出す文字数は4文字であることは分かっていますが、開始位置がバラバラです。最後の文字の位置はLENなので、その4文字前から開始すればよいです。
したがって、「=MID(A1,LEN(A1)-4,4)」となります。
MID関数で開始位置が一定でない場合で、右から数えた場所がNであれば、開始位置は「LEN-N+1」とします。
- =MID(文字列,LEN(文字列)-N+1,4)
別解
さきほどの設問で数字の左側の文字列を求めています。この文字数＋１が開始位置です。したがって、「=MID(A1,LEN(C1)+1,4)」となります。
（３）右から数えてN文字目まで
問題
B列は大文字＋ハイフン＋小文字＋数字である。B列の文字列からハイフンの右側にある小文字の文字列を取り出しなさい。
解説
文字列の途中から取り出す関数はMID関数です。取り出す開始位置は３であることは分かっていますが、文字数がバラバラです。文字数は、全体の文字数から、最初の文字とハイフンと最後の数字を除きます。全部で6文字なのでLEN(B1)-6となります。
「=MID(B1,3,LEN(B1)-6)」と入力します。
２．特定の文字を検索して左側・右側を取得する
（１）特定の文字の左側を取り出す
問題
A列にメールアドレスを入力した。アットマーク＠の左側がアカウント、右側がドメインである。アカウントの部分を取り出しなさい。
解説
文字列の左側を取り出すのはLEFT関数ですが、文字列が一定ではありません。しかも、＠の右側も一定ではありません。そこで、まずFIND関数を使って「@」の場所を求めます。
- =FIND("@",A1)
これは＠が左から何文字目かを表しているので、＠より左側にある文字数はこれより1少ないです。
したがって、文字数は「FIND("@",A1)-1」となります。
- =LEFT(A1,FIND("@",A1)-1)
特定の文字を検索してその左側を取得するときはLEFT関数を使います。取り出すときに検索した文字を含まない場合、その文字数は「FIND-1」となります。
- =LEFT(文字列,FIND(検索文字列,文字列)-1)
（２）途中から特定の文字までを取り出す
問題
アカウントのハイフンと＠の間にある文字列を取り出しなさい。
解説
文字列の途中を取り出すのはMID関数ですが、文字数は先ほどよりさらに2文字少ないです。つまりFIND-3です。
したがって、「=MID(A1,3,FIND("@",A1)-3)」となります。
（３）特定の文字の右側を取り出す
問題
ドメインの部分を取り出しなさい。
解説
文字列の右側を取得するのはRIGHT関数ですが、文字数が一定ではありません。しかし、さきほど左から数えて＠までの文字数をFIND関数で求めましたから、これを全体の文字数から引けばよいです。つまりLEN-FINDです。
したがって、「=RIGHT(A1,LEN(A1)-FIND("@",A1))」となります。
特定の文字を検索してその右側を取得するときはRIGHT関数を使い、その文字数は「LEN-FIND」とします。
- =RIGHT(文字列,LEN(文字列)-FIND(検索文字列,文字列))
３．特定の文字が見つからなかった場合の処理
（１）エラーの場合に空白にする
問題
A列の文字列から「～本部」を取り出しなさい。ただし、「本部」が無い場合は空白とする。
解説
A列の文字列から「本部」を検索します。「=FIND("本部",A1)」です。見つからない場合はエラーとなります。
また、先頭の文字を返すので「事業本部」の場合は３となります。
LEFT関数で文字列を取得するための文字数は＋１をしなければなりません。
したがって、「=LEFT(A1,FIND("本部",A1)+1)」となります。
エラーの場合は空白にします。
- =IFERROR(LEFT(A1,FIND("本部",A1)+1),"")
別解
LEFT関数の文字数（第2引数）を０にすると空白になります。エラーの場合に文字数を0にします。
- =LEFT(A1,IFERROR(FIND("本部",A1)+1,0))
（２）エラーの場合に全部取り出す
問題
A列の文字列から「～本部」以外の部分を取り出しなさい。また、C列の文字列から「～部」を取り出しなさい。ただし、「部」が無い場合はC列の文字列を全部取得しなさい。
解説
B列に本部を取り出しているので、MID関数で開始位置を変えればよいです。開始位置はB列の文字数＋１です。
なお、取り出す文字数は多めにしておけばよいのでLENにします。
- =MID(A1,LEN(B1)+1,LEN(A1))
B列を使わない場合、FINDで本部を探し、＋２をした位置が開始位置となります。
- =FIND("本部",A1)+2
FINDがエラーの場合は1です。
- =MID(A1,IFERROR(FIND("本部",A1)+2,1),LEN(A1))
C列の文字列から「部」を検索します。空白のセルや部が無いセルは、FINDで見つからないのでエラーとなります。
- =LEFT(C1,FIND("部",C1))
エラーの場合に元の文字列をそのまま取得します。
- =IFERROR(LEFT(C1,FIND("部",C1)),C1)
別解
元の文字列の最後に「部」を付けて検索をするという方法もあります。「=FIND("部",C1&"部")」と入力します。
このとき、部という文字があれば最初の部の位置を返しますが、無い場合は元の文字数を超える数値が返ります。
LEFT関数の文字数（第2引数）が元の文字数を超える場合は元の文字数をそのまま返します。
- =LEFT(C1,FIND("部",C1&"部"))
４．全体から一部を除いた残りの文字列
問題
A列の文字列からB列とD列で取り出した部分を除くすべてを抜き出しなさい。
解説
A列の文字列の右側の部分を取得するのでRIGHT関数を使います。取得する文字数はA列の文字数から、B列、D列の文字数を除いた数となります。
- =RIGHT(A1,LEN(A1)-LEN(B1)-LEN(D1))
別解
MID関数を用いる場合、開始位置はB列＋D列＋１です。文字数は多めに設定すればよいので、LEN(A1)とします。
- =MID(A1,LEN(B1)+LEN(D1)+1,LEN(A1))
５．特定の文字の2番目まで取り出す
問題
A列の文字列のうち「～本部～部」があればその部分を取り出し、なければ空白にしなさい。
解説
２つめの「部」を検索すればよいです。FINDの開始位置をFIND+1にします。
- =FIND("部",A1,FIND("部",A1)+1)
このとき、部が2つ無いものはエラーになりますので、０にします。
- =IFERROR(FIND("部",A1,FIND("部",A1)+1),0)
LEFT関数で取得します。
- =LEFT(A1,IFERROR(FIND("部",A1,FIND("部",A1)+1),0))
６．2つの文字に囲まれた部分を取り出す
（１）特定の文字から特定の文字まで
問題
A列のうちカッコの部分を取り出しなさい。ただし、カッコが無い場合は空白とする。
解説
文字列の途中を抜き出すのでMID関数です。MID関数の開始位置は、「（」の位置です。これはFIND関数で求められます。
- FIND("（",A1)
文字数は、「）」の位置から「（」の位置をひいて1を足します。
- FIND("）",A1)-FIND("（",A1)+1
MID関数で取り出します。
- =MID(A1,FIND("（",A1),FIND("）",A1)-FIND("（",A1)+1)
XとYが1文字の場合、XとYを含む文字列の中からX～Yを抜き出す数式は次の通りとなります。
- =MID(文字列,FIND("X",文字列),FIND("Y",文字列)-FIND("X",文字列)+1)
なお、カッコが無い場合はエラーになりますので、IFERROR関数で空白にします。
- =IFERROR(MID(A1,FIND("（",A1),FIND("）",A1)-FIND("（",A1)+1),"")
別解
いったん「（」の右側を取り出します。
- =RIGHT(A1,LEN(A1)-FIND("（",A1)+1)
この文字列について「）」の左側を取り出します。
- =LEFT(B1,FIND("）",B1))
エラーは空白にします。このように何回かに分けて取り出すと比較的簡単に取り出すことができます。1つの数式にする必要が無い場合は、無理に1つの数式にしようとせず、分けて考えたほうが分かりやすくなります。
- =IFERROR(C1,"")
（２）カッコの中身だけを取り出す
問題
A列のうちカッコの中の文字列だけを取り出しなさい。ただし、カッコが無い場合は空白とする。
解説
さきほどの問題の数式を一部修正します。MID関数の開始位置は、「（」の位置の次です。
- FIND("（",A1)+1
取り出される文字数は2文字少なくなります。
- FIND("）",A1)-FIND("（",A1)-1
MID関数で取り出します。
- =MID(A1,FIND("（",A1)+1,FIND("）",A1)-FIND("（",A1)-1)
XとYが1文字の場合、XとYを含む文字列の中からX～Yの中身だけを抜き出す数式は次の通りとなります。
- =MID(文字列,FIND("X",文字列)+1,FIND("Y",文字列)-FIND("X",文字列)-1)
なお、カッコが無い場合はエラーになりますので、IFERROR関数で空白にします。
- =IFERROR(MID(A1,FIND("（",A1)+1,FIND("）",A1)-FIND("（",A1)-1),"")
別解
いったん「（」の右側を取り出します。
- =RIGHT(A1,LEN(A1)-FIND("（",A1))
この文字列について「）」の左側を取り出します。
- =LEFT(B1,FIND("）",B1)-1)
エラーは空白にします。
- =IFERROR(C1,"")
（３）ハイフンからハイフンまで
問題
A列の文字列は、商品名、ハイフン、サイズ、ハイフン、カラーで構成されている。サイズを取り出しなさい。
解説
ハイフンとハイフンの間にある文字列を取り出します。途中にある文字列なのでMID関数です。開始位置は最初のハイフン＋１です。
- FIND("-",A1)+1
2番目のハイフンの位置は「FIND("-",A1,FIND("-",A1)+1)」です（参考：【Excel】LEN関数は文字数、FIND関数は左から何文字目にあるかを数える関数である）。
MIDで取り出す文字数は、2番目のハイフンの位置から、最初のハイフンの位置をひいて1を足します。
- FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1
したがって、次のような数式になります。
- =MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1)
Xが1文字の場合、X～Xの中身だけを抜き出す数式は次の通りとなります。
- =MID(文字列,FIND("X",文字列)+1,FIND("X",文字列,FIND("X",文字列)+1)-FIND("X",文字列)-1)
別解
いったんRIGHT関数で最初のハイフンの右側を取り出します。
- =RIGHT(A1,LEN(A1)-FIND("-",A1))
この文字列のハイフンの左側を取り出します。
- =LEFT(B1,FIND("-",B1)-1)
７．複数条件の取り出し
（１）複数の文字のいずれかを含むことの判定
問題
A列の文字列で「L」または「M」または「S」を含む場合に〇印をつけなさい。
解説
FIND関数は検索文字列が見つかれば数値を返し、見つからなければエラーを返します。そして、COUNT関数は数値をカウントするものであり、エラー値は数えません（COUNTはエラーにならない）。COUNT(FIND)の入れ子にすると、見つかれば1、見つからなければ0になります。
例えば、「=COUNT(FIND("L",A1))」とすると、「L」があるものは１、ないものは０となります。
「L」または「M」または「S」の場合は次のような式になります。「L」または「M」または「S」があれば1以上になります。
- =COUNT(FIND("L",A1),FIND("M",A1),FIND("S",A1))
さらに、配列を使って次のように書くこともできます。
- =COUNT(FIND({"L","M","S"},A1))
〇印をつけるのはCOUNTが1以上のときです。
- =IF(COUNT(FIND({"L","M","S"},A1)),"〇","")
（２）補足説明：セル範囲に入力されているものを含む
さきほどの問題で「L」「M」「S」が別のセルに入力されている場合は絶対参照をすればよいですが、INDEX関数で囲む必要があります。少し難しいですね。
- =IF(COUNT(INDEX(FIND($D$1:$D$3,A1),)),"〇","")
（３）いずれかの文字を含んでいればその左側を取り出す
問題
A列の文字列で、FIND関数を用いて「L」または「M」または「S」の位置を求めなさい。また、その位置より左側の文字列を取り出しなさい。
解説
A列の文字列に「LMS」を連結して「L」を検索します。
- =FIND("L",A1&"LMS")
Lが見つかった場合は最初に見つかった場所を返し、見つからない場合は文字数を超える値を返します。
これを利用してL、M、Sを検索してその最小値を求めます。
- =MIN(FIND({"L","M","S"},A1&"LMS"))
ただし、L、M、Sを全く含まない文字列の場合にエラーにならないので注意が必要です（さきほどの設問のようにIF関数を使う必要がある）。
LEFT関数で左側の文字列を取得します。これで「L」または「M」または「S」の左側の文字列を取得することができます。
- =LEFT(A1,MIN(FIND({"L","M","S"},A1&"LMS"))-1)
（４）左側にある数字の削除
問題
A列の文字列のうち数字の左側にある文字列を取り出しなさい。
解説
数字の左側を取り出すには、FINDで数字の位置を求めなければなりません。さきほどの問題で{"L","M","S"}としたのを、{1,2,3,4,5,6,7,8,9,0}とすればよいです。
- =LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1)
ちなみに、数字の部分はMID関数で取得できます。
- =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),LEN(A1))
（５）補足説明：左側の数字、右側の数字だけを取り出す
左側にある数字だけ、右側にある数字だけを取り出すのは、LOOKUPとINDIRECTが必要なので数式だけ紹介します。
- =LOOKUP(10^17,LEFT(A1,ROW(INDIRECT("A1:A"&LEN(A1))))*1)
- =LOOKUP(10^17,RIGHT(A1,ROW(INDIRECT("A1:A"&LEN(A1))))*1)
解説は以上です。
