転職先で周りから「できる！」と言われるためのOfficesoft極意まとめ

エクセル（Excel）関数で文字列を抽出する方法とは？　文字列抽出の極意

業務の中では、CSVファイルやほかのソフトからデータを取り込むこともあるかと思います。しかしそういったデータには乱れがあり、データベースとして不十分なケースも多いですよね。一つひとつデータを書き換えていては大変な時間がかかりますので、関数を使用してデータを整えましょう。

文字列の分割・結合は、関数を使いこなして意のままに

ここでは図のような顧客データを例に、集計や必要なデータの抽出を行いやすくするよう、データを整理していきます。

一つのセルから、ある条件を満たす文字列を抽出する：IF関数・MID関数・LEFT関数

都道府県ごとの集計や抽出を簡単に行いたい時は、都道府県と市区町村以下に住所を分割する必要があります。まずは、一つのセルに入力された住所から都道府県を取り出しましょう。

使用する関数はIF関数（論理関数）とLEFT関数・MID関数（文字列操作関数）です。IF関数は、ある条件を「満たす場合」と「満たさない場合」で処理を変える関数です。LEFT関数は、文字列の先頭から指定した数の文字を抽出します。 MID関数は、文字列の指定した位置から、指定した数の文字を抽出します。

この3つの関数を使って以下のような意味の関数式を組み立て、N列にある住所から都道府県のみを取り出していきましょう。式は言葉で論理的に組み立てていくと理解しやすくなりますよ。

「もしも、住所のセルの左から4番目が「県」ならば、左から4文字、それ以外は左から3文字取り出します。」
＝IF(MID(N2,4,1)=”県”,LEFT(N2,4),LEFT(N2,3))

1. 都道府県を表示したいセル（P2）を選択し、［数式］タブ→［関数ライブラリ］グループ→［論理］から、IF関数を起動します。
2. 論理式にMID関数（文字列操作関数）をネスト（※）し、左から4文字目を取り出して「県」かどうかを確かめていきます。名前ボックスからMID関数を起動し、次のように引数を入力します。
   ※ネストとは、関数の中に関数を組み入れること。
   
   文字列：住所が入力されたセル（N2）
   開始位置：何文字目から取り出したいのか。（4）
   文字数：取り出したい文字数（1）
   


3. 数式バーのIFのスペル内をクリックしてIFの引数画面に戻り、論理式に「="県”」と追加入力します。
   


4. 真の場合にカーソルを移動し、論理式と条件が合致した場合（TRUE）に表示する内容を指定していきます。ここでは文字列の左から「県」までを取り出したいため、名前ボックスからLEFT関数（文字列操作関数）をネストし、次のように引数を入力します。
   文字列：住所が入力されているセル（N2）
   文字数：取り出したい文字数（4）
   


5. 論理式と条件が合致しなかった場合（FALSE）に表示する内容も同様に、LEFT関数をネストして指定します。偽の場合には、真の場合で入力した数式をコピーして、文字数のみ4→3に修正しましょう。
   


6. ［OK］をクリックしてから、その列に数式をコピーします。
   

左から4文字目が「県」の場合のみ4文字、それ以外は3文字が取り出されました。

一つのセルの途中から文字列を抽出する：MID関数・LEN関数

住所のセルから市区町村以下を取り出す場合は、まずLEN関数（文字列操作関数）で都道府県の文字数を特定し、それ以降の文字を取り出すようにします。LEN関数は指定したセルの文字数をカウントする関数です。

1. 市区町村以下を表示したいセル（Q2）を選択し、［数式］タブ→［関数ライブラリ］グループ→［文字列操作］からMID関数を起動します。
2. 次のように引数を入力します。
   文字列：住所が入力されているセル（N2）
   開始位置は、住所から都道府県の文字数を引いた次の文字からとなるので、LEN関数をネストします。
3. 名前ボックスからLEN関数を起動し、次のように引数を入力します。
   文字列：文字数を数えたいセル（P2）
   


4. 数式バーのMIDのスペル内をクリックしてMIDの引数画面に戻ります。
5. 開始位置に「+1」を追加入力します。
   文字数：LEN関数をネストし「住所のセル（N2）の文字数－都道府県のセル（P2）の文字数」となるよう入力。
   


6. ［OK］をクリックしてから、その列に数式をコピーします。
   

異なるセルの文字列を結合する：CONCATENATE関数

取り出した市区町村と他の列に入力された番地を結合して一つのセルにまとめるにはCONCATENATE関数（文字列操作関数）を使用します。CONCATENATE関数は2つ以上の文字列を一つの文字列に結合します。

1. 住所を結合したいセル（R2）を選択し、［数式］タブ→［関数ライブラリ］グループ→［文字列操作］からCONCATENATE関数を起動します。
2. 次のように引数を入力します。
   文字列1：市区町村を取り出したセル（Q2）
   文字列2：番地が入力されたセル（O2）
   


3. OKをクリックしてから、その列に数式をコピーします。
   

ポイント

カタカナ・数字を全角で統一したい場合は、CONCATENATE関数の前にJIS関数を組んでおきます。

不要な列を削除する：値貼り付けのショートカット

不要となった元データをそのまま削除すると、数式を組んだ列の参照先がなくなりエラーとなります。不要な列は、値貼り付けでセルから数式を抜き、値のみとした後に削除しましょう。

値貼り付けは、Ctrlキー+Cでコピーした後、ALTキー→Hキー→Vキー→Vキーの順で押して行います。

ポイント

行・列の挿入削除も、ショートカットキーが便利です。
行・列の挿入：Ctrlキー+「+」
行・列の削除：Ctrlキー+「－」
不要な列を削除する前に、参照先となっている列から数式を抜くことを忘れないようにしましょう。

文字の細かな変換は、関数の活用でスピードに差が付く！

文字列操作関数の活用【1】　文字を検索してデータを抽出する：FIND関数・LEFT関数

セルに入力されたデータから指定した文字の前後を抜き出すには、文字を検索するFIND関数を使用します。FIND関数は、指定した文字列を指定したセルに入力された文字列の中で検索し、探し当てた文字列の先頭が左から何文字目にあるかを表示します。

この関数を使って、メールアドレスからアカウント（＠の前のアルファベット）を抜き出しましょう。

1. アカウントを表示したいセル（E2）を選択し、［数式］タブ→［関数ライブラリ］グループ→［文字列操作］からLEFT関数を起動します。
2. 次のように引数を入力します。
   文字列：メールアドレスが入力されているセル（D2）


3. 文字数には、「＠」の前までの文字数を調べるために名前ボックスからFIND関数をネストし、次のように引数を入力します。
   検索文字列：セル内の文字列で探したい文字（＠）
   対象：メールアドレスが入力されているセル（D2）
   開始位置は、省略します。
   


4. 数式バーのLEFTのスペル内をクリックしてLEFTの引数画面に戻り、文字数の引数に「-1」を追加入力します。
   FIND関数では「＠」の文字位置を求めます。取り出したい文字は＠の前までなので、「-1」となります。
   


5. ［OK］をクリックしてから、その列に数式をコピーします。
   

文字列操作関数の活用【2】　アルファベットを大文字・小文字に統一する、頭文字のみ大文字に変換する：UPPER関数・LOWER関数・PROPER関数

セルに入力されたアルファベットを大文字に揃えたい場合は、UPPER関数を使用します。

1. 大文字のアカウントを表示したいセル（F2）を選択し、［数式］タブ→［関数ライブラリ］グループ→［文字列操作］からUPPER関数を起動します。
2. 引数にアカウントのセル（E2）を指定します。
3. ［OK］をクリックしてから、その列に数式をコピーします。
   
   
   全く同じ方法で、小文字に統一する場合はLOWER関数を、頭文字のみを大文字に変換する場合はPROPER関数を使用します。
   

文字列操作関数の活用【3】　文字数を指定して削除する：REPLACE関数

REPLACE関数は、文字を置換する関数です。置換後の文字を空白に指定することで削除が可能となります。この関数は、「左から何文字目を開始位置とし、何文字分をまとめて何に置換するか」という考え方で設定します。

例えば携帯電話番号の局番をすべて削除したい時、「090-」を空白に置換することとなりますが、「080-」も混在しています。文字で指定すると何度も置換を行うこととなりますが、REPLACE関数なら一度に置換できます。「左から4文字分」を置換すると考えれば、「090-」でも「080-」でも4文字を置換すれば良いことになります。

1. 置換後の携帯番号を表示したいセル（L2）を選択し、［数式］タブ→［関数ライブラリ］グループ→［文字列操作］からREPLACE関数を起動します。
2. 次のように引数を入力します。
   文字列：携帯番号が入力されたセル。（K2）
   開始位置：何文字目から置換したいのか。（1）
   文字数：何文字分置換したいのか。（4）
   置換文字列：削除したいので空白。（””）


3. ［OK］をクリックしてから、その列に数式をコピーします。

文字列操作関数の活用【4】　文字をまとめて置換する：SUBSTITUTE関数

REPLACE関数では、置換する文字を文字数で指定しましたが、SUBSTITUTE関数では、文字を指定して置換します。電話番号のようにセル内に同じ文字「－」が2カ所に含まれている場合、最初の「－」は「（」に、2つ目の「－」は「）」に置換することができます。

1. 置換後の携帯番号を表示したいセル（M2）を選択し、［数式］タブ→［関数ライブラリ］グループ→［文字列操作］からSUBSTITUTE関数を起動します。
2. 次のように引数を入力します。
   文字列：カーソルがある状態で、名前ボックスからSUBSTITUTE関数をネストします。
3. ネストした引数画面に、次のように入力します。
   文字列：携帯電話番号が入力されたセル（K2）
   検索文字列：置換したいハイフンの文字（"-"）
   置換文字列：ハイフンを何に置換したいのか。（”（”）
   置換対象：セル内の何番目にあるハイフンなのかを指定。（1）
   


4. 数式バーのSUBSTITUTEのスペル内をクリックして引数画面を戻り、次のように引数を入力します。
   検索文字列：置換したいハイフンの文字（"-"）
   置換文字列：ハイフンを何に置換したいのか。（”）”）
   置換対象：セル内の何番目にあるハイフンなのか。（1）
   
   すでに一つ目のハイフンは置換済みとなっているので、2つ目のSUBSTITUTE関数の置換対象のハイフンも「1」となります。
   


5. ［OK］をクリックしてから、その列に数式をコピーします。
   

文字列操作関数を活用することにより、データの精査を効率良く行うことができます。「面倒だなぁ」「大変だなぁ」「これもっと簡単にできないかなぁ」と思うことがあれば、関数を組み合わせて工夫すると、意外とできてしまうことが多いものです。

一つひとつの関数をしっかりと理解して、オリジナルの方法を見つけてみるのもエクセルの楽しさですね。

編集／株式会社スペースシップ