文字列データを分割したり、必要な部分だけを抽出したりするには、複数の関数を組み合わせて「文字抽出のアルゴリズム」を再現しなければならない。ということで、今回は文字列データの分割・抽出に活用できる関数を“おさらい”しておこう。これらを上手に組み合わせられるようになれば、それだけ応用範囲が広がるはずだ。
住所を「都道府県」と「それ以降」に分割する
前回の連載では、「都道府県が省略されている住所」から「都道府県」を抽出する方法を紹介した。ここまで複雑な処理でなくても、文字列の分割・抽出を行うときに「関数の複合的な知識」を問われるケースは少なくない。
文字列の分割や抽出に使える関数の一覧
それぞれの関数の使い方を説明する前に、前回の連載で中途半端になっていた「住所データの分割処理」を済ませておこう。以降に紹介する作業も、文字列データを分割する手法の一例として参考にして頂ければ幸いだ。
前回の連載では「住所」から「都道府県」を取得するところまで作業した。これを「都道府県」、「それ以降の住所」、「建物名・部屋番号」の3つに分割してみよう。「それ以降の住所」を抽出する方法は第39回の連載でも紹介しているが、今回の例は「最初から都道府県が省略されている住所」が混在しているのが異なる点となる。
変換リストをもとに「都道府県」の列を作成したデータ表
このような場合も関数SUBSTITUTEを使って「それ以降の住所」を抽出することが可能だ。具体的には、「住所」の文字列データを対象に、「都道府県」の文字を「空文字」に置き換える、という処理を行えばよい。これを関数で記述すると以下の図のようになる。
関数SUBSTITUTEを使った「それ以降の住所」の抽出
あとは、この関数をオートフィルでコピーするだけ。これで「それ以降の住所」を99.99％くらいの確率で正しく抽出できる。
「住所」の列には「最初から都道府県が省略されている住所」も含まれているが、特に問題なく処理できる。この場合、置換する文字（都道府県）が見つからないため、結果として「もとの住所データ」がそのまま取得されることになる。
オートフィルで関数SUBSTITUTEをコピーした様子
ただし、かなりの低確率ではあるが、「以降の住所」を正しく取得できないケースがある。それは「△△県営住宅」のように、住所の一部に「都道府県」と同じ名称が使用されているケースだ。
「それ以降の住所」を正しく取得できないケース
この場合、「都道府県」を示す文字ではない部分が「空文字」に置き換えられてしまう。第4引数に「1」を指定して、1番目の検索結果だけを置換するようにしても、上図のように思い通りに機能してくれないケースがある。非常にレアなケースではあるが、このような不具合にも備えるとなると、別のアルゴリズムを考えなければならない。
ということで、今度は「最初の数文字」に着目する方法で「それ以降の住所」を抽出してみよう。このアルゴリズムは、以下のような考え方になる。
（1）関数LENで「都道府県」の文字数（N）を調べる
（2）関数LEFTで「住所」の先頭からN文字を抽出する
（3）手順（2）の結果が「都道府県」と同じか？ を関数IF調べる
（4-1）TRUEの場合：「住所」の先頭からN文字を「空文字」に置換する
（4-2）FALSEの場合：「住所」をそのまま出力する
これを関数で記述すると、以下の図のようになる。
IF、LEFT、LEN、REPLACEを組み合わせた方法
あとは、この関数をオートフィルでコピーするだけだ。すると、以下の図のような結果になり、全データの「それ以降の住所」を正しく取得できる。
オートフィルで関数をコピーした様子
ここまでの作業が済んだら「都道府県」や「住所1」のデータを「関数」ではなく、「文字列データ」に上書きしてしまってもよい。この処理は、第40回の連載で紹介した「値の貼り付け」を使って実現する。
建物名・部屋番号を「住所2」として分割する
続いては、「それ以降の住所」から「建物名・部屋番号」を分離する方法を紹介していこう。以下の図のように「建物名の前」にスペースが挿入されている場合は、関数を使用しなくてもデータを分割できる。「それ以降の住所」の列を選択し、「データ」タブにある「区切り位置」をクリックする。
列を選択して「区切り位置」コマンドをクリック
データの分割方法を指定するウィザードが表示されるので、最初に分割方法を指定する。「コンマやタブなどの区切り文字によって・・・」を選択し、「次へ」ボタンをクリックする。
分割方法の指定
次は「区切り文字」を指定する。今回の例の場合「スペース」をONにしてから「次へ」ボタンをクリックすればよい。
区切り文字の指定
最後に、分割後の各列のデータ形式を指定する。住所の場合は「文字列」を指定するのが基本となるが、初期設定の「G/標準」のままでも特に問題は生じないだろう。データ形式の指定が済んだら「完了」ボタンをクリックする。
データ形式の指定
スペース文字の部分で区切られてデータが2列に分割される。あとは「住所2」などの見出しを作成するだけ。これで「それ以降の住所」を「住所1」と「住所2」（建物名・部屋番号）に分割できる。
2列に分割された住所
このように適切な位置にスペースが挿入されていれば問題なく処理を進められるが、以下の図のようにスペースがない場合は、非常に困った状況になってしまう。
「区切り位置」では分割できない住所
この場合、何らかのアルゴリズムにより文字列データを分割していく必要があるが、それが非常に難しい作業になる。
・建物名の前は「?-?-?」になるケースが多い
・建物名は「カタカナ」で始まる場合が多い
などの傾向はあるものの、絶対的な法則ではない。3丁目などの「丁目」がなく、4桁の地番がそのまま使われている住所もあるし、「☆☆荘」のように漢字で始まる建物名もある。
47個しかない「都道府県」と違って「住所」はかなり自由度の高い文字列になるため、それを適切に分割するアルゴリズムは見つからない・・・、という状況に陥ってしまう。この場合、関数でデータを分割することは不可能だ。
よって、手作業でスペースを挿入していき、その後、「区切り位置」コマンドで2つの列に分割する、というのが現実的な処理方法になるかもしれない。実は、本連載で使用しているデータ表も、前回までは「建物名の前」にスペースが挿入されていなかった。そこに手作業でスペースを挿入することにより、「区切り位置」コマンドを利用可能な状態にしている。
文字列データはどのように保管しておくべきか
先ほど述べたように、文字列データの分割・抽出は、必ずしも適切なアルゴリズムが見つかるとは限らない問題になる。一方、分割されているデータを1つにまとめる「文字列の結合」は、どんな状況でも問題なく実行できる。文字を連結する「&」だけで解決できるケースが大半を占めるといえるだろう。
このような視点で考えると、再利用する可能性があるデータは「文字列データを分割した状態」のまま保管しておくのが基本といえる。文字列の結合は簡単に行えるが、いぢと結合してしまった文字列を分割するのは非常に難しい、もしくは「手作業でないと無理」という状況になってしまう。
文字列の分割・抽出が難しいことを知っていれば、データの保管方法にも配慮できるようになるはずだ。
文字列データの分割・抽出に使える関数
最後に、今回の連載の本題でもある「文字列の分割・抽出に使える関数」を簡単にまとめておこう。
基本になるのは関数LEFT、RIGHT、MIDの3つ。「文字列の先頭」からN文字分を抽出したいときは関数LEFT、「文字列の末尾」からN文字分を抽出したいときは関数RIGHTを指定する。「中間にある文字」を抽出したいときは関数MIDを使用する。こちらは、N文字目からM文字分という具合に2つの数値で位置を指定する必要がある。
◆関数LEFTの書式
=LEFT(文字列, 文字数)
◆関数RIGHTの書式
=RIGHT(文字列, 文字数)
◆関数MIDの書式
=MID(文字列, 開始位置, 文字数)
一部の文字を置換したり、削除したりするときは、SUBSTITUTEやREPLACEといった関数を使用する。「置換前」の文字を「置換後」の文字に置き換えるときは関数SUBSTITUTE、置換する位置を「N文字目からM文字分」という形で指定するときは関数REPLACEを使用する。どちらも置換後の文字に「空文字」を指定することで、文字を削除する関数として利用することが可能だ。
◆関数SUBSTITUTEの書式
=SUBSTITUTE(文字列, 置換前, 置換後, [何番目])
◆関数REPLACEの書式
=REPLACE(文字列, 開始位置, 文字数, 置換文字列)
これらの関数だけで実現できる処理は、比較的簡単な処理といえる。そうでない場合は、「キーとなる文字が何文字目にあるか？」、「全体の文字数は何文字か？」などを調べてアルゴリズムを構築していく必要がある。
「キーとなる文字が文字目にあるか？」を調べるときは、関数FINDを使用する。その結果は「N文字目」のNが数値データとして返される。「全体の文字数は何文字か？」は関数LENで調べられる。こちらはカッコ内にセル参照を指定するだけで、そのセルに入力されている文字数を数値データとして取得できる。
◆関数FINDの書式
=FIND(キーワード, 文字列, [開始位置])
◆関数LEN
=LEN(文字列)
これらの情報をもとに条件分岐するときは、関数IF（またはIFS）を使って分岐処理を実現する。なお、関数FINDは「キーワード」に指定した文字が見つからなかった場合にエラーが発生する仕様になっている。こういった「エラーの場合」の処理を指定するときに関数IFERRORを使用する。
◆関数IFの書式
=IF(条件, 真の場合, 偽の場合)
◆関数IFERRORの書式
=IFERROR(数式・関数, エラー時の値・処理)
ほかにも文字列の分割・抽出に活用できる関数は色々とあるが、まずは上記で紹介した9個の関数の使い方をマスターしておくことをお勧めする。これだけでも、それなりに幅広い問題に対応できるようになるはずだ。それ以外の関数は、必要になった時点で「こんなことを実現できる関数またはテクニックはないか？」をネット検索してみるとよいだろう。
