エクセルの文字列関数でセルの文字列を自由自在に分割! LEFT関数・MID関数・RIGHT関数・LEN関数・FIND関数

スポンサーリンク

エクセルの文字列操作関数はたくさんあります。今回は、LEFT関数・MID関数・RIGHT関数・LEN関数・FIND関数を用いて住所から都道府県・市・区・町名に分割する方法です。

スポンサーリンク

長さの違う文字列を条件に当てはめて分割する

A列には、下記のように都道府県・市・区・町名が一つになっているデータが入っています。

都道府県・市・区・町名をエクセルの関数で分割する

今回は、住所から長さの違う文字列をさまざまな条件を当てはめて分割する方法です。

完成図は下記のようになります。

都道府県・市・区・町名に分割

それでは、まずは練習として長さが決まっている文字列を取り出す方法からスタートします。

文字列の左から指定した数の文字列を取り出すLEFT関数

まずは住所から、都道府県名を取り出します。今回は長さが決まっていますので簡単です。

長さが決まっている文字列の、左から文字数を指定して取り出す「LEFT関数」の挿入からです。

B2セルを選択して、「関数の挿入」をクリックします。

関数の挿入

「関数の検索」ボックスに「left」と入力し、「検索開始」ボタンをクリックすると、「関数名」の候補に「LEFT」関数が表示されますので、選択して「OK」をクリックします。

関数の検索

LEFT関数は2つの引数を必要とします。

=LEFT(抜き出したい文字列のセル,左から抜き出す文字数)

ここでは抜き出したい文字列のセルはA2セル、左から抜き出す文字数は3ですね。

それぞれ入力し、「OK」をクリックします。

LEFT関数の引数は2つ

「OK」をクリックすると下記のように文字列の左から3文字の「大阪府」が表示されています。

左から3文字を抜き出した

ドラッグしてB10セルまで数式をコピーします。2行目から10行目まで抜き出すことができました。

都道府県を抜き出した

途中の文字を抜き出すには「MID関数」

続いて住所の途中にある「市」を取り出します。

文字列の途中を取り出すには「MID関数」を使用します。

C2セルを選択し、「関数の挿入」をクリックします。

「関数の挿入」

「関数の検索」に「mid」と入力し、「検索開始」ボタンをクリックして「MID関数」を表示させます。

「MID関数」の挿入

「MID関数」は3つの引数を必要とします。

「=MID(抜き出したい対象の文字列,抜き出したい開始位置,抜き出したい文字数)」

となります。

ここでは「市」が4文字目から、「文字数」が3ですから、下図のように引数を設定し、「OK」をクリックします。

「MID関数」の引数は3つ

文字列の途中を取り出すことができました。数式をC10セルまでドラッグします。

途中の文字列を取り出した

すべてのセルで、文字列の途中の文字を取り出しました。

すべてのセルで文字列を抜き出し

文字列の長さが違う場合は「FIND関数」で文字の位置を調べる

ここからは少し難しくなります。

文字列の長さが違う「区」の文字を取り出すには、取り出す文字列の長さを調べる必要があります。

そこで、すべてのセルに「区」という文字がありますので、「区」の文字列が先頭から何番目かを調べる関数、「FIND関数」を使用します。

VBAを利用せずリストを予測候補表示(サジェスト機能)で絞り込む方法!エクセルのドロップダウンリスト(プルダウンメニュー)は便利なようで結構めんどい。。。
エクセルのドロップダウンリスト(プルダウンリスト)って、便利なようですがリストが増えると選択するのが大変なんですよね。入力候補が多数あると、入力したい値を探すのに一苦労です。入力規則があるとデータが多いときに選択が大変...

D2セルを選択し、「関数の挿入」をクリックします。

区はセルによって文字列の長さが違う

「関数の検索」ボックスに「find」と入力し、「検索開始」ボタンをクリックして「FIND関数」を選択後、「OK」をクリックします。

「FIND関数」の挿入

まずは、「FIND関数」の結果を確認することから始めます。

「=FIND(検索したい文字列,文字列を検索する対象のセル)」

となります。3つ目の引数は省略可能です(検索する開始文字位置が先頭ではない場合に指定します)。

「FIND関数」の引数は基本2つ

「大阪府大阪市北区梅田」という文字列で、「区」という文字は先頭から8番目であることが分かります。

同様に、D2セルの数式をD10セルまでコピーします。

該当の文字が先頭から何番目かを取得

すると、下図のようにそれぞれのセルの中で「区」という文字列が何番目かを取得することができます。

それぞれの文字列中から「区」という文字が何番目かを取得

それでは、この「FIND関数」を利用して、「区」の文字列を取り出します。

一旦、D2~D10セルの内容をクリアし、改めてD2セルを選択後、「関数の挿入」をクリックし、「MID関数」を挿入します。

改めて関数の挿入

ここでは先ほど使用した「MID関数」で文字数を設定します。

「区」取り出すには、「開始位置」が今回は決まっているので「大阪府大阪市」という文字列の次、7を指定します。

そしてそこから取り出す文字数を、「FIND関数」を利用して決めます。

「大阪府大阪市北区梅田」という文字列では、先ほど調べたように「区」という文字列は8番目です。ですから、「FIND関数」で見つけた8文字目から「大阪府大阪市」という6文字を引いた数が「区」の長さになります。

「区」の文字列の長さの取得

「MID関数」3つ目の引数、「文字数」はこのように「FIND関数」によって取得します。3つ目の引数「FIND(“区”,A2)-6」と入力します(この場合は””(ダブルクォーテーションマーク)が必要です)。

その後、「OK」ボタンをクリックします。

「MID関数」3つ目の引数は「FIND関数」を利用する

数式は「=MID(A2,7,FIND(“区”,A2,7)-6)」となります。

無事、下図のように「北区」という文字を取り出すことができました。D2セルの数式をD10セルまでドラッグしてコピーします。

「区」の文字列を抜き出し

「区」の文字数に関わらず、すべてのセルで「区」の文字列を取り出すことができていることを確認してください。

文字数が違っても抜き出し可能

文字列を右から取り出す「RIGHT関数」と文字列の長さを取得する「LEN関数」

最後に、町名を取り出します。ここでは、文字列を右から抜き出す「RIGHT関数」と、文字列の長さを取得する「LEN関数」を利用します。また先ほどの「FIND関数」も利用します。

まずは「LEN関数」から説明いたします。

E2セルをクリックし、「関数の挿入」をクリックします。

関数の挿入

「関数の検索」ボックスに「len」と入力し、「検索開始」ボタンをクリックして、「LEN関数」を選択後、「OK」をクリックします。

「LEN関数」を挿入

「LEN関数」自体は簡単です。引数も1つ(文字列)だけで、指定したセルの文字数を取得します。

「LEN関数」の引数は1つ

E2セルに「LEN関数」を入力し、E10セルまでコピーした後が、下図です。

A列の各セルの文字数が表示されています。今回はこれを利用して、右から「町名」を取り出します。

セルの文字数を取得する「LEN関数」

今回は、右から指定した文字数の文字列を取得する「RIGHT関数」も利用します。

一旦、E2~E10セルをクリアし、E2セルを選択して「関数の挿入」をクリックします。

「関数の検索」ボックスに「right」入力して「検索開始」ボタンをクリック、「RIGHT関数」を挿入します。

「RIGHT関数」の挿入

「RIGHT関数」は2つの引数を必要とします。

「=RIGHT(抜き出したい文字列を含むセル,右から抜き出したい文字数)」

となります。

抜き出したい文字数は、先ほどの「FIND関数」も利用します。

文字数全体「区」という文字がある文字の位置=町名の文字数となります。

全体の文字数-「区」という文字の位置=町名の文字数

「RIGHT関数」の引数は以下のようになります。

文字数の引数には「RIGHT(A2,LEN(A2)-FIND(“区”,A2,1))」と入力しています。()の数に注意してください。

「OK」ボタンをクリックして確定させます。

「RIGHT関数」の文字数に「LEN関数」と「FIND関数」を利用

無事町名を取得することができました。

E2セルの数式をE10セルまでコピーします。

数式をコピー

以下のように無事、文字数が違う場合も町名を取得することができました。

「住所」から「都道府県」「市」「区」「町名」を抜き出した

文字列関数で頻繁に使用するのは4つ

文字列関数で頻繁に使用するのは「LEN関数」「MID関数」「RIGHT関数」「LEN関数」の4つです。

そこに、文字列を検索する「FIND関数」を利用して今回のように住所から自由自在に文字列を抜き出し・取り出しすることが可能です。

ただし「都道府県」がバラバラで、「市」や「区」がない住所が並んでいる場合はもう少し複雑な式になります。

「都道府県」がバラバラで「市」や「区」がない場合の対処は大変

こちらについては後日、別記事にて紹介いたします。

スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言

コメント

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