【スプレッドシート】TEXTSPLIT関数の代わりにSPLIT関数を使おう！

ExcelのTEXTSPLIT関数のように、Googleスプレッドシートで文字を分割できる関数をご紹介します。SPLIT関数といくつかの関数を組み合わせることで、文字を縦方向に分割したり、大文字と小文字を区別せずに分割する方法も解説しているので、ぜひ参考にしてみてください。

はじめに

Excelで文字分割に便利なTEXTSPLIT関数ですが、GoogleスプレッドシートにはTEXTSPLIT関数がありません。もし、スプレッドシートで文字分割をしたい場合は、SPLIT関数を使います。

TEXTSPLIT関数の使用例：メールアドレスを@の前後で分割します。

数式：=TEXTSPLIT(A2,"@")

SPLIT関数の使い方

基本的な使い方

SPLIT関数は、指定した「区切り文字」を使って、ひとつの文字列を複数のセルに分割する関数です。

たとえば、A列にあるメールアドレスを「@」の前後で分割したい場合、数式は次のようになります。

例：=SPLIT(A2,"@")

各文字での分割

SPLIT関数の第3引数「各文字での分割」は、区切り文字をどのように扱うかをTRUEまたはFALSEで指定します。

• FALSE：区切り文字を「単語」として扱う
  • 例： =SPLIT("apple and banana", "and", FALSE)
  • apple と banana に分割されます。
• TRUE(省略可）：区切り文字を「1文字ずつ」として扱う
  • 例： =SPLIT("apple and banana", "and", TRUE)
  • pple と b に分割されます。

空のテキストを削除する

SPLIT関数の第4引数「空のテキストを削除」は、結果から空のテキストを削除するかどうかをTRUEまたはFALSEで指定します。

連続した区切り文字がある場合に生成される空のテキストは、デフォルトでは自動的に削除されますが、FALSEを指定すると、そのまま表示されます。

例：=SPLIT(A2,"@",,FALSE)

SPLIT関数でTEXTSPLIT関数の機能を再現する

行方向への分割

文字列を行方向へ分割したい場合は、SPLIT関数にTRANSPOSE関数を組み合わせます。

たとえば、A列にある複数のメールアドレスを「@」で分割し、それぞれを行に表示したい場合は、次の数式を使います。

例：=ArrayFormula(TRANSPOSE(SPLIT(A2:A4,"@")))

この数式では、A2:A4という範囲を一度に処理するため、[Ctrl]+[Shift]+[Enter]を押してArrayFormula関数を追加します。

分割後の不足値を埋め合わせる

分割後の空白セルを特定の文字で埋め合わせたい場合は、SPLIT関数、ISBLANK関数、IF関数を組み合わせます。

たとえは、A列にあるメールアドレスを「@」で分割し、空白セルは「-」で埋め合わせたい場合、数式は次のようになります。

例：=ArrayFormula(IF(ISBLANK(SPLIT(A2:A4, "@")), "–", SPLIT(A2:A4, "@")))

数式の解説

1. SPLIT関数が、テキストを「@」で分割します。
2. ISBLANK関数は、SPLIT関数の結果が空白ならTRUEを、そうでなければFALSEを返します。
3. IF関数は、ISBLANK関数の結果を判定し、TRUEなら「-」を、FALSEならSPLIT関数の結果をそのまま表示します。

大文字・小文字を区別しない分割

区切り文字として、大文字と小文字を区別せずに分割したい場合、SPLIT関数にREGEXREPLACE関数を組み合わせます。

たとえば、A列にあるテキストを、「and」という文字列で大文字・小文字を区別せずに分割したい場合、次の数式を使います。

例：=SPLIT(REGEXREPLACE(A2, "(?i)and", ","), ",")

数式の解説

• REGEXREPLACE(...): この部分で、テキスト内の「and」と「AND」を、すべて「,」という統一された区切り文字に置き換えます。
  • (?i)は、「正規表現を大文字・小文字を区別せずに一致させる」という特殊なオプションです。
• SPLIT(..., ","): 置き換えられた後の文字列を、「,」を区切り文字として分割します。