乱雑なデータを分割して整える

インポートしたデータが整っていることは、ほとんどありません。たとえば、1つのセルに John,Smith,Sales と入っているデータを、実際には3つの列に分けたい場合があります。

Google Sheets の SPLIT 関数は、区切り文字の位置で文字列を分割し、隣接するセルに各部分を展開します。

この最後のレッスンでは、ここまでに学んだ正規表現のスキルに SPLIT と CLEAN を組み合わせ、乱雑なテキストをきれいに整えます。

SPLIT(text, delimiter) は、text 内で delimiter が見つかった位置で分割し、各部分を右側のそれぞれのセルに出力します。

次の式は、A2 をカンマで分割します。John,Smith,Sales は3つのセルに展開され、それぞれ John、Smith、Sales が入ります。

デフォルトでは、区切り文字列内の各文字がそれぞれ独立した区切り文字として扱われます。そのため、SPLIT(A2, ",;") はカンマとセミコロンの両方で分割します。

次の式は、カンマまたはスペースで分割します。行によって区切り文字が異なる場合に便利です。

SPLIT には省略可能な引数があります。3番目の引数は各区切り文字を個別に分割するかどうかを制御し、4番目の引数は空の結果を削除するかどうかを制御します。

デフォルトでは空の部分が削除されるため、列の位置がずれることがあります。次の式では最後の引数に FALSE を渡して空の部分を残し、A,,C の列の対応関係を維持します。

PDF やウェブページから貼り付けたテキストには、数式や検索を妨げる見えない制御文字が含まれていることがよくあります。

CLEAN 関数は、文字列からほとんどの印字不可能な文字を削除します。次の式は、A2 からそのような不要な文字を取り除いた結果を返します。見た目は同じなのに一致しなかった値も、正しく扱えるようになります。

CLEAN は制御文字を処理しますが、余分なスペースは処理しません。先頭、末尾、連続するスペースを削除する TRIM と組み合わせてから、分割します。

次の式は、制御文字を除去し、スペースを整えたうえで、結果をカンマで分割します。1つの式で処理しています。内側から読んでみましょう。

A列に Smith , John のような、余分なスペースを含む乱雑なデータが入っているとします。きれいな姓と名を別々のセルに分けたいとします。

次の式は、スペースを整えてからカンマで分割します。分割後の各部分を個別に TRIM で囲めば、それぞれの部分も整えられますが、通常は先にクリーニングしておけば十分です。

SPLIT では、区切り文字を文字どおり指定する必要があります。区切り文字が一定しない場合は、まず REGEXREPLACE で統一してから、標準の区切り文字で分割します。

次の式は、スペース、カンマ、セミコロンが連続する部分を1つのパイプに置き換え、そのパイプで分割します。入力の形式が大きくばらついていても、きれいに処理できます。

セルの一部だけを取り出してから、それを分割したい場合があります。Tags: red, blue, green から、色だけを別々のセルにしたいとします。

次の式は、まずコロンより後ろをすべて抽出し、その後でカンマによって分割します。正規表現と分割の機能を組み合わせることで、真価を発揮します。

ほとんどの乱雑なインポートデータでは、次の順番が効果的です。

• 制御文字を削除するCLEAN
• スペースを整えるTRIM
• 区切り文字を統一したり不要な文字を取り除いたりするREGEXREPLACE
• 列に分割するSPLIT

次の式は、1つのセルに対してこの4つの手順を順番に適用します。

SPLIT はデフォルトで各部分を列方向に展開します。フィルタリングやカウントに使えるように、値を1行に1つずつ縦方向に並べたい場合もあります。

SPLIT を TRANSPOSE で囲むと、展開された行を列に変換できます。次の式は A2 をカンマで分割し、結果を縦方向に積み重ねます。カンマ区切りの一覧を、検索に使いやすい整った列に変換するのに最適です。

分割とクリーニングについて、理解度を確認しましょう。

Google Sheets で分割とクリーニングの機能を組み合わせ、コースを修了しました。

• SPLIT(text, delimiter) は、セルを展開された列に分割します
• 追加の引数で、複数文字の区切り文字や空の部分を制御できます
• CLEAN は見えない制御文字を除去し、TRIM はスペースを整えます
• 分割する前に REGEXREPLACE を使うと、乱雑な区切り文字を統一できます
• CLEAN、TRIM、REGEXREPLACE、SPLIT を連結すると、繰り返し使えるクリーニング手順になります

これで、一致、抽出、置換、分割、クリーニングを行う正規表現ツールキットが完成しました。