スポンサーリンク

【Excel/関数】ザックリ解説 文字列操作関数～（私にもできたんだから）あなたにもできる！エクセル関数を使いこなそう⑥ ～

エクセル関数 エクセル
2020.08.04
スポンサーリンク
※当サイトは広告を含みます

関数はこの５つさえ知っておけばだいたいOKと書きました。

①IF
②COUNTIF
③SUMIF
④VLOOKUP
⑤LEFT,RIGHT,MID（文字列取り出し関数）

今回はそのうちの ⑤ LEFT, MID, RIGHT関数 をざっくり解説。

最初に訂正！

その他にもLEN関数、FIND関数も⑤の仲間に加えたくなりました。

なので⑤のLEFT, MID, RIGHT, LEN, FIND関数をざっくり解説。

■LEFT関数

指定した文字列の「左から何文字目まで」を取り出してね、という関数です。

＝LEFT( 文字列 , 取り出したい文字数 ) と指定します。

例えば

＝LEFT( “あいうえお” , 3 )

だったら、返す答えは「あいう」です。

ですが、例のように直接文字列を指定することは少ないです。

だいたいは下の図のようにセルを指定することが多いです。

セルA1に入力してある文字列の左から３文字目までを取り出しする式をセルC1に入力しています。青い吹き出しがセルC1に入力してある関数です。

LEFT関数のダイアログボックスは下図のようになります。

一つ目の欄にセルや文字列を指定し、二つ目の欄には取り出したい文字数を入力します。

文字数の入力を省略すると、１文字だけ取り出します。（省略することはあまりないのですが、たまに入力を忘れると１文字だけ返ってくる。。。）

指定した文字列より多い文字数を入力すると文字列全体が返ります。

= LEFT ( A1 , 100 )

の結果は

普通に「あいうえお　12345」です。おしりに余分な空白文字列が入るのでは？と思いきや、そんなことはなくそのまんま文字列を返してくれます！

■RIGHT関数

LEFT関数の逆で、RIGHT関数は「右から何文字目まで」を取り出します。

先ほどの例でセルA1の「右から３文字目まで」を取り出しています。

LEFT関数も、RIGHT関数も半角、全角問わず〇文字を取り出します。

■MID関数

指定した文字列の〇文字目から〇文字を取り出したい、と指定します。

先ほどの例でセルA１の「４文字目」から「５文字」を取り出しています。

空白も１文字と数えます。

MID関数のダイアログボックスは下図のような感じです。

LEFT関数のところで、多めの取り出し文字数を入力すると文字列全体が返ります、というのがありましたが、MID関数ではそれが便利に使えます。

下の例で、「東京都」より右の住所を取り出したいとします。

セルを指定し、東京都は３文字なので、それより右の「４」文字目から多めに「20文字取り出す」と指定します。

= MID ( A1 , 4 , 20 )

多めに取り出し文字数を指定しても余分な空白が入るなどの支障がないのでよいですね。

■組み合わせると便利　LEN関数、FIND関数（SERCH関数）　　ついでにTRIM関数、VALUE関数

◇ LEN関数

LEN関数は指定した文字列の「文字数」を数えます。

いや～、これが何かと使うんですよね。IF関数やMID関数と組み合わせたり、桁数が決まっているものの入力間違いがないか？を探すために使ったり、VBAでも使ったり、案外便利な関数です。

＝LEN ( 文字列 ) 　と指定します。

＝LEN(“あいうえお”)　だったら5文字なので答えは「5」です。

やっぱり直接文字列を指定することはほぼなく、セルを指定することが大半です。

例です。A列の文字数を数えるLEN関数をB列に入力しています。

2行目：数値でも文字数をカウントします

3行目：全角半角区別なく文字数をカウントします

4行目：空白も文字数をカウントします。4行目には「う」と「え」の間に空白一つ、「お」の後にも見えませんが空白を２つ入力しています

5行目：折り返していても問題なく文字数をカウントします

6行目：「=LEFT(A5,1)」という関数が入力してあります。関数自体の文字数ではなく返ってきた答えの文字数をカウントしていますね

なお、関数の答えがエラー値だった場合、LEN関数は「＃VALUE!」等の文字数をカウントするのではなく、エラー値となります。

〇ついでの TRIM関数

文字列から余分なスペース（空白）を除去します。

上の4行目のように後ろに余分な空白があるが、空白を除去した文字列にしたい

というときに使います。

＝TRIM(A4)

で「あいう　えお」になります。真ん中の空白は除去されません。

空白を除去したうえで文字数をカウントしたい場合は

＝ LEN ( TRIM ( A4 ) )

とします。

システムで吐き出した氏名などの文字列でたまに余分な空白が後ろについてくることがあるので、そんなときに使います。

ちなみにTRIM関数では、文字列の中のスペースは1つの場合は残しますが、２つ以上ある場合はスペースは1つ以外は除去されます。（山田□□太郎□□ → 山田□太郎) 気が利いてますね。

◇ FIND関数、SEARCH関数

文字列の中からある文字列を探して何文字目にあるかを教えてくれる関数です。

 FIND関数もSEARCH関数もほとんど同じように使えます。

違いは

FIND関数は大文字小文字を区別する。ワイルドカード(＊)文字を検索します（逆に言うとワイルドカードを使った検索はできません）

SEARCH関数は大文字小文字を区別しません。ワイルドカードを使った検索ができます

詳しくはこちらのサイトが分かりやすかったです

「SEARCH」と「FIND」の違い：エクセル(EXCEL)関数

普段使う分にはあんまり違いは気にならないので、ここではFINDを使うことにします。

＝FIND ( 探したい文字 , 探されたい文字列 , [何文字目から探すか] )

[何文字目から探すか] は省略可能で、省略した場合は1文字目から探します。

例です。

A列のセル「山田山太郎」から「山」が何文字目にあるかを探します。

B列に関数が入力してあります。

C列にはB列に入力してある式を表示しています。

2行目は開始位置を省略しているため１文字目から探しています。

答えは１。「１文字目に山があります」との答えです

3行目は開始位置を「2」としているため２文字目から順に探しています。

答えは3。検索開始位置が「2」なので最初の１文字目の山は検索対象となりません。なので二つ目の「山」の位置を返しています。

開始位置は2ですが、2文字目から探して2文字目ですよ、ではなく、返す答えは1文字目から数えた文字位置を返します。

■ LEFT,MID,RIGHT関数とLEN,FINDなどを組み合わせた例

例１：A列の製品コードと品番が「ハイフン(－)」でつなげられたものを、B列に製品コード、C列に品番と分けて取り出したい。製品コードは桁数が決まっていないが、品番は5桁と桁数が決まっている場合の例です。

B列、C列に入力した式を吹き出しで表示しています。

6行目で解説します。

まず、C列から。品番は5桁と決まっているので簡単です。

品番取り出しの式：A列の右から5文字を取り出す式を入力します。

＝RIGHT ( A6 , 5 ) 

製品コードは・・・

6行目の「A333-12349」の例の場合

右から数えて5桁の品番とハイフンで6文字・・・

製品コード+品番の全体の文字数から6文字を引いたら・・・

A333-12349は10文字。10-6＝４。お！左から4文字を取り出せば製品コードになる！要するに LEN(A6) が全体の文字数。ここから6を引いた文字数を取り出せばよい。

なので

=LEFT ( A6, LEN(A6) – 6 )

とします。

〇 ついでの VALUE関数

上の例でC列に取り出した品番は数値のようでいて文字列になっています。

LEFT,MID,RIGHT関数で取り出したものは数字であっても文字列となります。

数字にしたいよう、という場合はVALUE関数を使います。

VALUE(“数値に変換したい文字列”)と指定します。VALUEの中に入れるのはセル位置参照、関数、文字列そのままでもOKです。

上の例で品番を取り出し後数値に変換したい場合は

＝VALUE ( RIGHT ( A6 , 5) )

とします。

または、単純に１をかけても数値になります

= RIGHT ( A6 , 5) ＊ 1

どちらも万が一文字列だった場合はエラーになりますので、文字列と数値が混在する可能性がある場合はIFERROR関数で文字列だった場合の逃げ道を残しておくとよいです。

=IFERROR( RIGHT ( A6 , 5 ) ＊ 1 , RIGHT ( A6 , 5 ) )

これで数値の場合は×１して数値となり、数値変換がエラーとなる文字列の場合はそのままRIGHT関数が適用となります。

例２：A列の製品コードと品番が「ハイフン(－)」でつなげられたものを、B列に製品コード、C列に品番と分けて取り出したい。製品コード・品番とも桁数が決まっていない場合の例です。

B列、C列に入力した式を吹き出しで表示しています。

6行目「A2-1234」で解説します。

B列製品コードの取り出し方です。

左から「－」がある１文字前までを取り出せば大丈夫そうです。

ハイフンが何文字目にあるかをFIND関数で出します。

FIND ( “-” , A6 )

ハイフンは3文字目にあるので「3」が返ります。

左から3文字目まで取り出すとハイフンまで入ってしまうので、1を引きます。

FIND ( “-” , A6 ) – 1

これで2文字目まで取り出してね、が指定できます。

で、LEFT関数を使い、〇文字目まで取り出してのところに先ほどのFIND関数を入れます。

=LEFT ( A6 , FIND ( “-” , A6 ) – 1 )

これで製品コードが取り出せます。

C列品番の取り出し方です。

「A2-1234」ではハイフンが3文字目にあります。

それを利用して

＝MID ( A6 , FIND( “-” , A6 )+1 , 100 )

・・・4文字目(ハイフンのある位置+1)から100文字取り出してね(品番は100桁もあればもれなく取り出せるだろうということで100)

または

= MID ( A6 , FIND( “-” , A6 )+1 , LEN(A6) – FIND( “-” , A6 ) )

・・・4文字目(ハイフンのある位置+1)から、全体の文字数 から ハイフンのある文字位置を引いた文字数を取り出してね
(例では全体の文字数「7」、ハイフンのある文字数「3」で7-3=4が取り出すべき品番の文字数)

または表示した吹き出しのように

= RIGHT ( A6 , LEN(A6) – FIND( “-” , A6 ) )

・・・右から、全体の文字数 から ハイフンのある文字位置を引いた文字数を取り出してね

(先ほどと同様。例では全体の文字数「7」、ハイフンのある文字数「3」で7-3=4が取り出すべき品番の文字数)

と、いろいろやりましたが、上の例の場合エクセルの「区切り位置」機能を使うのが一番手っ取り早いですね(*’▽’)

リボンの「データ」から「区切り位置」を選択し、「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択。

その他にチェックを入れてハイフンを入力（下図）

 そうすると「－」で分かれてくれます。

ただA列のデータが置き換わってしまうので、製品コード＋品番の元データも残したいようでしたらA列のデータをコピーしてから「区切り位置」の処理をします。

余談でした。

例３：A列の住所からB列に区名だけを取り出したい。東京都が省略されている場合もある。

 式が長くなります。

=IF(LEFT(A2,3)=”東京都”, MID(A2,4,FIND(“区”,A2)-3) , LEFT(A2,FIND(“区”,A2)) )

まず最初に、IF式で最初の3文字が「東京都」かどうかを調べて、その後の処理を分けます。

最初の3文字が「東京都」の場合赤字の式が適用されます。

MID ( A2 , 4 , FIND ( “区” , A2 ) – 3 )

MID関数で東京都(3文字)の次の4文字目から取り出しを開始、

FIND関数で「区」が何文字目にあるかを調べて「東京都」の3文字を引きます。

4行目「東京都世田谷区」の例だと

「区」は7文字目にあります。7から東京都の3文字を引くと「4」。

「東京都世田谷区」の4文字目から4文字を取り出すと「世田谷区」になります。

5行目「東京都目黒区」だと、「区」は6文字目にあります。6から東京都の3文字を引くと「3」。「目黒区」が取り出せます。

最初の3文字が「東京都」ではない場合、青字の式が適用されます。

LEFT ( A2 , FIND ( “区” , A2 ) )

「区」が何文字目にあるかFIND関数で調べて、LEFT関数で左からその分の文字数を取り出します。 

2行目の「中央区〇〇」は「区」が3文字目にあります。左から3文字を取り出せば「中央区」が取り出せます。

ややこしいですよね。

最初からMIDうんちゃらかんちゃらと書こうとすると、こんがらがるので別の列にLEN関数やFIND関数で実際に数字を出しながら考えると式を組み立てやすいと思います。

ああああ、また長くなってしまった。

関数は5つでOKと言いながら、ついでと言いながらいくつも関数を紹介してしまった( ;∀;)

以上でこのシリーズは終わりです。

ざっくり解説しようと思ったもののザックリではなくしつこく解説になってしまってすみません。

最後までお読みいただきありがとうございました！

関数はこの５つさえ知っていればOK！シリーズ

スポンサーリンク

コメント

タイトルとURLをコピーしました