わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

Excelで特定の文字を検索して文字列の一部を抽出する【LEN、FIND、LEFT、RIGHT、MID】

LEFT、RIGHT、LEFT関数で文字列の一部を取り出すことができますが、文字数が一定でない場合はどうすればよいでしょうか。

例えば、特定の文字を検索して見つかったらその文字まで取り出して、見つからなかったら空白にするとか、特定の文字間にある文字列を取り出すとか、右から数えて文字を取り出すとか、複数の文字のいずれかの左側を取り出すとか・・・挙げたらきりがありませんが、LEN関数やFIND関数と組み合わせることによって、複雑な文字列の抽出が可能となります。

そこで、今回は、LEN、FIND、LEFT、RIGHT、MIDを組み合わせて文字列を取り出す練習問題を出題します。

f:id:waenavi:20191027210342j:plain

 

 

目次

0.文字列の抽出をするのに必要な関数(復習)

この記事ではおもに5つの関数を使用します。これらの違いを理解したうえで演習をしましょう。

(1)文字列の取り出し LEFT関数、RIGHT関数、MID関数

LEFT、RIGHT、MID関数は文字列を取り出す関数なので「文字列」が返されます。これらの基本問題についてはこちらの記事をご覧ください。

(2)文字位置の計算 LEN関数、FIND関数

LEN、FIND関数は文字数又は特定の文字の位置をカウントする関数なので「整数値」が返されます。これらの基本問題についてはこちらの記事をご覧ください。

上記の記事で何をやったのか、おさらいをしておきましょう。

  • LEN、FINDは全角も半角も1文字としてカウントする
  • FINDは左から何文字目かを数える
  • FINDで複数文字を検索する場合
  • IFERRORでエラーになっているものを強制的に0にする
  • 都道府県名の文字数
  • 空白セル、空白文字列の関係
  • 特定文字列の2番目、3番目を求める
  • FIND関数の開始位置(第3引数)の仕様

 

1.右から数えた文字数の指定

(1)右側のN文字を削除

問題

A列は英字+数字4文字+英字1文字である。A列の文字列から数字の左側にあるアルファベットの文字列を取り出しなさい。

f:id:waenavi:20191026212611j:plain

 

解説

文字列の先頭から取り出す関数はLEFT関数です。しかし、取り出す文字数がバラバラです。文字数は、全体の文字数から、数字4文字と最後の英字を除きます。したがってLEN(A1)-5となります。

f:id:waenavi:20191027214319j:plain

 

「=LEFT(A1,LEN(A1)-5)」と入力します。これは後ろの5文字を削除しているのと同じです。

f:id:waenavi:20191026213046j:plain

 

LEFT関数で取得する文字数が一定でない場合で、右側の取得しない文字列の文字数がNであれば、文字数は「LEN-N」とします。

  • =LEFT(文字列,LEN(文字列)-N)

 

(2)右から数えてN文字目からM文字取得

問題

A列の文字列から数字4文字を取り出しなさい。

f:id:waenavi:20191026212611j:plain

 

解説

文字列の途中から取り出す関数はMID関数です。取り出す文字数は4文字であることは分かっていますが、開始位置がバラバラです。最後の文字の位置はLENなので、その4文字前から開始すればよいです。

f:id:waenavi:20191026213511j:plain

 

したがって、「=MID(A1,LEN(A1)-4,4)」となります。

f:id:waenavi:20191026213624j:plain

 

MID関数で開始位置が一定でない場合で、右から数えた場所がNであれば、開始位置は「LEN-N+1」とします。

  • =MID(文字列,LEN(文字列)-N+1,4)

 

別解

さきほどの設問で数字の左側の文字列を求めています。この文字数+1が開始位置です。したがって、「=MID(A1,LEN(C1)+1,4)」となります。

f:id:waenavi:20191026214516j:plain

 

(3)右から数えてN文字目まで

問題

B列は大文字+ハイフン+小文字+数字である。B列の文字列からハイフンの右側にある小文字の文字列を取り出しなさい。

f:id:waenavi:20191026212611j:plain

 

解説

文字列の途中から取り出す関数はMID関数です。取り出す開始位置は3であることは分かっていますが、文字数がバラバラです。文字数は、全体の文字数から、最初の文字とハイフンと最後の数字を除きます。全部で6文字なのでLEN(B1)-6となります。

f:id:waenavi:20191026214921j:plain

 

「=MID(B1,3,LEN(B1)-6)」と入力します。

f:id:waenavi:20191026214952j:plain

 

2.特定の文字を検索して左側・右側を取得する

(1)特定の文字の左側を取り出す

問題

A列にメールアドレスを入力した。アットマーク@の左側がアカウント、右側がドメインである。アカウントの部分を取り出しなさい。

f:id:waenavi:20191026215259j:plain

 

解説

文字列の左側を取り出すのはLEFT関数ですが、文字列が一定ではありません。しかも、@の右側も一定ではありません。そこで、まずFIND関数を使って「@」の場所を求めます。

  • =FIND("@",A1)

f:id:waenavi:20191026215425j:plain

 

これは@が左から何文字目かを表しているので、@より左側にある文字数はこれより1少ないです。

f:id:waenavi:20191026220349j:plain

 

したがって、文字数は「FIND("@",A1)-1」となります。

  • =LEFT(A1,FIND("@",A1)-1)

f:id:waenavi:20191026220436j:plain

 

特定の文字を検索してその左側を取得するときはLEFT関数を使います。取り出すときに検索した文字を含まない場合、その文字数は「FIND-1」となります。

  • =LEFT(文字列,FIND(検索文字列,文字列)-1)

 

(2)途中から特定の文字までを取り出す

問題

アカウントのハイフンと@の間にある文字列を取り出しなさい。

f:id:waenavi:20191026215259j:plain

 

解説

文字列の途中を取り出すのはMID関数ですが、文字数は先ほどよりさらに2文字少ないです。つまりFIND-3です。

f:id:waenavi:20191026220752j:plain

 

したがって、「=MID(A1,3,FIND("@",A1)-3)」となります。

f:id:waenavi:20191026220843j:plain

 

(3)特定の文字の右側を取り出す

問題

ドメインの部分を取り出しなさい。

f:id:waenavi:20191026215259j:plain

 

解説

文字列の右側を取得するのはRIGHT関数ですが、文字数が一定ではありません。しかし、さきほど左から数えて@までの文字数をFIND関数で求めましたから、これを全体の文字数から引けばよいです。つまりLEN-FINDです。

f:id:waenavi:20191026221259j:plain

 

したがって、「=RIGHT(A1,LEN(A1)-FIND("@",A1))」となります。

f:id:waenavi:20191026221359j:plain

 

特定の文字を検索してその右側を取得するときはRIGHT関数を使い、その文字数は「LEN-FIND」とします。

  • =RIGHT(文字列,LEN(文字列)-FIND(検索文字列,文字列))

 

3.特定の文字が見つからなかった場合の処理

(1)エラーの場合に空白にする

問題

A列の文字列から「~本部」を取り出しなさい。ただし、「本部」が無い場合は空白とする。

f:id:waenavi:20191026221644j:plain

 

解説

A列の文字列から「本部」を検索します。「=FIND("本部",A1)」です。見つからない場合はエラーとなります。

f:id:waenavi:20191026221832j:plain

 

また、先頭の文字を返すので「事業本部」の場合は3となります。

f:id:waenavi:20191026221950j:plain

 

LEFT関数で文字列を取得するための文字数は+1をしなければなりません。

f:id:waenavi:20191026222621j:plain

 

したがって、「=LEFT(A1,FIND("本部",A1)+1)」となります。

f:id:waenavi:20191026222701j:plain

 

エラーの場合は空白にします。

  • =IFERROR(LEFT(A1,FIND("本部",A1)+1),"")

f:id:waenavi:20191026222845j:plain

 

別解

LEFT関数の文字数(第2引数)を0にすると空白になります。エラーの場合に文字数を0にします。

  • =LEFT(A1,IFERROR(FIND("本部",A1)+1,0))

f:id:waenavi:20191026222928j:plain

 

(2)エラーの場合に全部取り出す

問題

A列の文字列から「~本部」以外の部分を取り出しなさい。また、C列の文字列から「~部」を取り出しなさい。ただし、「部」が無い場合はC列の文字列を全部取得しなさい。

f:id:waenavi:20191026223014j:plain

 

解説

B列に本部を取り出しているので、MID関数で開始位置を変えればよいです。開始位置はB列の文字数+1です。

f:id:waenavi:20191026223500j:plain

 

なお、取り出す文字数は多めにしておけばよいのでLENにします。

  • =MID(A1,LEN(B1)+1,LEN(A1))

f:id:waenavi:20191026223159j:plain

 

B列を使わない場合、FINDで本部を探し、+2をした位置が開始位置となります。

  • =FIND("本部",A1)+2

f:id:waenavi:20191026223627j:plain

 

FINDがエラーの場合は1です。

  • =MID(A1,IFERROR(FIND("本部",A1)+2,1),LEN(A1))

f:id:waenavi:20191026223707j:plain

 

C列の文字列から「部」を検索します。空白のセルや部が無いセルは、FINDで見つからないのでエラーとなります。

  • =LEFT(C1,FIND("部",C1))

f:id:waenavi:20191026223811j:plain

 

エラーの場合に元の文字列をそのまま取得します。

  • =IFERROR(LEFT(C1,FIND("部",C1)),C1)

f:id:waenavi:20191026223858j:plain

 

別解

元の文字列の最後に「部」を付けて検索をするという方法もあります。「=FIND("部",C1&"部")」と入力します。

f:id:waenavi:20191026224204j:plain

 

このとき、部という文字があれば最初の部の位置を返しますが、無い場合は元の文字数を超える数値が返ります。

f:id:waenavi:20191026224324j:plain

 

LEFT関数の文字数(第2引数)が元の文字数を超える場合は元の文字数をそのまま返します。

  • =LEFT(C1,FIND("部",C1&"部"))

f:id:waenavi:20191026224443j:plain

 

4.全体から一部を除いた残りの文字列

問題

A列の文字列からB列とD列で取り出した部分を除くすべてを抜き出しなさい。

f:id:waenavi:20191026224636j:plain

 

解説

A列の文字列の右側の部分を取得するのでRIGHT関数を使います。取得する文字数はA列の文字数から、B列、D列の文字数を除いた数となります。

  • =RIGHT(A1,LEN(A1)-LEN(B1)-LEN(D1))

f:id:waenavi:20191026224733j:plain

 

別解

MID関数を用いる場合、開始位置はB列+D列+1です。文字数は多めに設定すればよいので、LEN(A1)とします。

  • =MID(A1,LEN(B1)+LEN(D1)+1,LEN(A1))

f:id:waenavi:20191026224817j:plain

 

5.特定の文字の2番目まで取り出す

問題

A列の文字列のうち「~本部~部」があればその部分を取り出し、なければ空白にしなさい。

f:id:waenavi:20191026225110j:plain

 

解説

2つめの「部」を検索すればよいです。FINDの開始位置をFIND+1にします。

  • =FIND("部",A1,FIND("部",A1)+1)

f:id:waenavi:20191026225257j:plain

 

このとき、部が2つ無いものはエラーになりますので、0にします。

  • =IFERROR(FIND("部",A1,FIND("部",A1)+1),0)

f:id:waenavi:20191026225340j:plain

 

LEFT関数で取得します。

  • =LEFT(A1,IFERROR(FIND("部",A1,FIND("部",A1)+1),0))

f:id:waenavi:20191026225426j:plain

 

6.2つの文字に囲まれた部分を取り出す

(1)特定の文字から特定の文字まで

問題

A列のうちカッコの部分を取り出しなさい。ただし、カッコが無い場合は空白とする。

f:id:waenavi:20191027201629j:plain

 

解説

文字列の途中を抜き出すのでMID関数です。MID関数の開始位置は、「(」の位置です。これはFIND関数で求められます。

  • FIND("(",A1)

f:id:waenavi:20191027201911j:plain

 

文字数は、「)」の位置から「(」の位置をひいて1を足します。

  • FIND(")",A1)-FIND("(",A1)+1

f:id:waenavi:20191027202201j:plain

 

MID関数で取り出します。

  • =MID(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1)

f:id:waenavi:20191027202323j:plain

 

XYが1文字の場合、XYを含む文字列の中からXYを抜き出す数式は次の通りとなります。

  • =MID(文字列,FIND("X",文字列),FIND("Y",文字列)-FIND("X",文字列)+1)

f:id:waenavi:20191027202530j:plain

 

なお、カッコが無い場合はエラーになりますので、IFERROR関数で空白にします。

  • =IFERROR(MID(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1),"")

f:id:waenavi:20191027202619j:plain

 

別解

いったん「(」の右側を取り出します。

  • =RIGHT(A1,LEN(A1)-FIND("(",A1)+1)

f:id:waenavi:20191027202650j:plain

 

この文字列について「)」の左側を取り出します。

  • =LEFT(B1,FIND(")",B1))

f:id:waenavi:20191027202723j:plain

 

エラーは空白にします。このように何回かに分けて取り出すと比較的簡単に取り出すことができます。1つの数式にする必要が無い場合は、無理に1つの数式にしようとせず、分けて考えたほうが分かりやすくなります。

  • =IFERROR(C1,"")

f:id:waenavi:20191027202820j:plain

 

(2)カッコの中身だけを取り出す

問題

A列のうちカッコの中の文字列だけを取り出しなさい。ただし、カッコが無い場合は空白とする。

f:id:waenavi:20191027201629j:plain

 

解説

さきほどの問題の数式を一部修正します。MID関数の開始位置は、「(」の位置の次です。

  • FIND("(",A1)+1

f:id:waenavi:20191027202939j:plain

 

取り出される文字数は2文字少なくなります。

  • FIND(")",A1)-FIND("(",A1)-1

f:id:waenavi:20191027203048j:plain

 

MID関数で取り出します。

  • =MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

f:id:waenavi:20191027203138j:plain

 

XYが1文字の場合、XYを含む文字列の中からXY中身だけを抜き出す数式は次の通りとなります。

  • =MID(文字列,FIND("X",文字列)+1,FIND("Y",文字列)-FIND("X",文字列)-1)

なお、カッコが無い場合はエラーになりますので、IFERROR関数で空白にします。

  • =IFERROR(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1),"")

f:id:waenavi:20191027203305j:plain

 

別解

いったん「(」の右側を取り出します。

  • =RIGHT(A1,LEN(A1)-FIND("(",A1))

f:id:waenavi:20191027203436j:plain

 

この文字列について「)」の左側を取り出します。

  • =LEFT(B1,FIND(")",B1)-1)

f:id:waenavi:20191027203527j:plain

 

エラーは空白にします。

  • =IFERROR(C1,"")

f:id:waenavi:20191027203607j:plain

 

(3)ハイフンからハイフンまで

問題

A列の文字列は、商品名、ハイフン、サイズ、ハイフン、カラーで構成されている。サイズを取り出しなさい。

f:id:waenavi:20191027203711j:plain

 

解説

ハイフンとハイフンの間にある文字列を取り出します。途中にある文字列なのでMID関数です。開始位置は最初のハイフン+1です。

  • FIND("-",A1)+1

2番目のハイフンの位置は「FIND("-",A1,FIND("-",A1)+1)」です(参考:【Excel】LEN関数は文字数、FIND関数は左から何文字目にあるかを数える関数である)。

f:id:waenavi:20191027204749j:plain

 

MIDで取り出す文字数は、2番目のハイフンの位置から、最初のハイフンの位置をひいて1を足します。

  • FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1

したがって、次のような数式になります。

  • =MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1)

f:id:waenavi:20191027204852j:plain

 

Xが1文字の場合、XXの中身だけを抜き出す数式は次の通りとなります。

  • =MID(文字列,FIND("X",文字列)+1,FIND("X",文字列,FIND("X",文字列)+1)-FIND("X",文字列)-1)

別解

いったんRIGHT関数で最初のハイフンの右側を取り出します。

  • =RIGHT(A1,LEN(A1)-FIND("-",A1))

f:id:waenavi:20191027205017j:plain

 

この文字列のハイフンの左側を取り出します。

  • =LEFT(B1,FIND("-",B1)-1)

f:id:waenavi:20191027205100j:plain

 

7.複数条件の取り出し

(1)複数の文字のいずれかを含むことの判定

問題

A列の文字列で「L」または「M」または「S」を含む場合に〇印をつけなさい。

f:id:waenavi:20191027205153j:plain

 

解説

FIND関数は検索文字列が見つかれば数値を返し、見つからなければエラーを返します。そして、COUNT関数は数値をカウントするものであり、エラー値は数えません(COUNTはエラーにならない)。COUNT(FIND)の入れ子にすると、見つかれば1、見つからなければ0になります。

例えば、「=COUNT(FIND("L",A1))」とすると、「L」があるものは1、ないものは0となります。

f:id:waenavi:20191027205250j:plain

 

「L」または「M」または「S」の場合は次のような式になります。「L」または「M」または「S」があれば1以上になります。

  • =COUNT(FIND("L",A1),FIND("M",A1),FIND("S",A1))

f:id:waenavi:20191027205324j:plain

 

さらに、配列を使って次のように書くこともできます。

  • =COUNT(FIND({"L","M","S"},A1))

f:id:waenavi:20191027205356j:plain

 

〇印をつけるのはCOUNTが1以上のときです。

  • =IF(COUNT(FIND({"L","M","S"},A1)),"〇","")

f:id:waenavi:20191027205443j:plain

 

(2)補足説明:セル範囲に入力されているものを含む

さきほどの問題で「L」「M」「S」が別のセルに入力されている場合は絶対参照をすればよいですが、INDEX関数で囲む必要があります。少し難しいですね。

  • =IF(COUNT(INDEX(FIND($D$1:$D$3,A1),)),"〇","")

f:id:waenavi:20191027205608j:plain

 

(3)いずれかの文字を含んでいればその左側を取り出す

問題

A列の文字列で、FIND関数を用いて「L」または「M」または「S」の位置を求めなさい。また、その位置より左側の文字列を取り出しなさい。

f:id:waenavi:20191027205753j:plain

 

解説

A列の文字列に「LMS」を連結して「L」を検索します。

  • =FIND("L",A1&"LMS")

f:id:waenavi:20191027205830j:plain

 

Lが見つかった場合は最初に見つかった場所を返し、見つからない場合は文字数を超える値を返します。

f:id:waenavi:20191027210106j:plain

 

これを利用してL、M、Sを検索してその最小値を求めます。

  • =MIN(FIND({"L","M","S"},A1&"LMS"))

f:id:waenavi:20191027210255j:plain

 

ただし、L、M、Sを全く含まない文字列の場合にエラーにならないので注意が必要です(さきほどの設問のようにIF関数を使う必要がある)。

LEFT関数で左側の文字列を取得します。これで「L」または「M」または「S」の左側の文字列を取得することができます。

  • =LEFT(A1,MIN(FIND({"L","M","S"},A1&"LMS"))-1)

f:id:waenavi:20191027210342j:plain

 

(4)左側にある数字の削除

問題

A列の文字列のうち数字の左側にある文字列を取り出しなさい。

f:id:waenavi:20191027210433j:plain

 

解説

数字の左側を取り出すには、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)

f:id:waenavi:20191027210522j:plain

 

ちなみに、数字の部分はMID関数で取得できます。

  • =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),LEN(A1))

f:id:waenavi:20191027210558j:plain

 

(5)補足説明:左側の数字、右側の数字だけを取り出す

左側にある数字だけ、右側にある数字だけを取り出すのは、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)

f:id:waenavi:20191026225700j:plain

 


解説は以上です。


 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]