本ページには広告が含まれています。

TEXTSPLIT関数 区切り文字でテキストを列と行に分割できる

TEXTSPLIT関数 区切り文字で文字列を列と行に分割できる

TEXTSPILIT関数は、2022年8月下旬のアップデートで新たに一般公開された14個の文字列・検索/行列関数のひとつです。(3月から「Office Insider Program」でテストされていました)
ただし、現時点で使用できるのは「Microsoft 365」の最新ExcelとWeb版に限られます。

TEXTSPILITは、テキスト文字列操作関数の一つです。文字列を指定した区切り文字で列と行に分割して、別々のセルにスピル表示させることができます。

スポンサーリンク

はじめに

TEXTSPLIT関数は、区切り位置指定ウィザードと同じような動作を関数で実行します。表示先のセルにTEXTSPLIT関数の数式をセットすることで列方向と行方向に分割させることができます。
これはTEXTJOIN関数ARRAYTOTEXT関数の機能を反転させたような感じの動作です。

くるみこ
くるみこ

TEXTSPLIT関数は、今のところサブスクリプション版の「Microsoft 365」アプリ「Excel for Microsoft 365」とWeb版だけで使用できます。基本的な使い方について紹介していきます。

振り分けてセルに表示するにはスピル機能が無いとできない動作ですね。
詳しく教えてください。よろしくお願いしますm(__)m

【この記事でわかることは】
・TEXTSPLIT関数の使い方がわかります

TEXTSPLIT 関数の構文

TEXTSPLIT 関数入力時に表示される「数式オートコンプリート」から確認します。
※ 対応していないExcelバージョンでは「オートコンプリート」にこの関数は表示されません。
リンクはこの関数の公式ヘルプの掲載場所です。

TEXTSPLIT 関数の構文と引数

TEXTSPLIT関数設定時のオートコンプリート

表示される引数の表示画像がこちらです。

TEXTSPLIT関数入力時の数式オートコンプリート引数表示

=TEXTSPLIT(text,col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
引数が英文表記のため分かりずらいのでこの関数のヘルプなどで確認して整理しました。

【構文】

=TEXTSPLIT(文字列, 列区切り, [行区切り], [空を無視], [一致モード], [埋めるセル値])
 ※[ ]内の引数は省略可能

引数説明
文字列
text
分割するテキスト文字列【必須】
列区切り
col_delimiter
列を分割する文字または文字列【必須】
[行区切り]
[row_delimiter]
行を分割する文字または文字列 [省略可能]
[空を無視]
[ignore_empty]
区切り文字の間が空の場合どうするかを指定
FALSE →【規定値】指定は空のセルを作成する
TRUE → 指定で空のセルは作成せず無視する
[一致モード]
[match_mode]
0 →【規定値】大文字と小文字を区別する
1 → 指定で大文字と小文字を区別しない
[埋めるセル値]
[pad_with]
最大列数に不足している行を埋めるセルの値
※値を指定しない場合 0(数値)になる
【省略時の規定】#N/A で埋められる
※[ ]内の引数は省略可能

下の画像はHELPに表記されている引数 [ignore_empty] 以下の説明部分のスクショです。

HELPに表記されている引数 [ignore_empty] 以下の説明部分のスクショ
※ HELP の説明文の一部を引用のためスクリーンショットしています。

前記事 VALUETOTEXT関数 のHELP説明文でも間違いがありましたが、TEXTSPLIT関数でも複数の間違いを発見しました。翻訳のせいなのかわかりませんが、公式のHELPでも間違いはあることを念頭において、実際に動かして確認してみることが大事ですね。

TEXTSPLIT 関数の使用例

それではいくつかの使用例を見ていきましょう。

区切り文字の指定方法

TEXTSPLIT関数で必須な引数は「分割する文字列」と「列の区切り文字」です。
「区切り文字」の指定方法がいくつかありますので、まずはその使用例を見てみましょう。

・分割対象の文字列は式内に記述するのではなく、A1セルの文字列をセル参照するようにしました。
・TEXTSPLIT関数式は、A2セルにセットしています。

単一文字で指定

[列区切り]を単一文字で指定した例

【[列区切り]を単一文字で指定した場合】
・2番目の引数「列区切り」に指定した区切り文字は “,” カンマの一文字です。
・区切り文字で分割された文字が「列方向(右横方向)」各セルにスピルされました。

単一文字「改行文字」で指定

列区切り文字をCHAR(10)で指定した例
・セル内改行されている文字列を「改行コード」を使って分割する例

【CHAR(10)を指定した場合】
・画像のとおりA1セルは「セル内改行」された文字列となっています。
・「列区切り」に改行コード「CHAR(10)」をセットすれば改行位置で分割できます。

文字列で指定

区切り文字に「文字列」を指定した例

・”<>” は2文字の文字列になっています。
・「区切り文字列」の位置で分割された文字が各セルにスピルされました。
・もっと長い文字列でも問題なく分割可能です。

複数の種類を指定する方法

配列{,}を使って複数の区切り文字を指定することができます。

複数(2種類)の区切り文字を配列で指定した例
・2種類の「区切り文字」を指定した例
複数(4種類)の区切り文字を配列で指定した例
・4種類の「区切り文字」を指定した例

【配列で複数文字を指定した場合】
・”<” と ”>” の2文字を配列として {“<“,”>”} 括っています
・2例目は5種類ですが、同様に配列に入れて区切り文字を指定しています。
・動作としては、配列内の文字を検索して一致していたら区切るという動作です。
・もっと多種を指定しても問題なく分割可能できます。

「列区切り」と「行区切り」その他の引数

4つ目の引数以降を指定しない場合

[空を無視] が FALSE の場合

【 [空を無視] が FALSE の場合 】
4つ目以降の引数 [空を無視] を省略した場合、FALSE を指定したのと同じです
・[空を無視] の規定値は FALSE なので、空のセルを作成します。
・「6」と「8」にあたる部分が「空」なので空白のセルが作成されています。

[空を無視] が TRUE の場合

[空を無視] が TRUE の場合

[空を無視] が TRUE なら、空のセルを無視します。
・結果「6」と「8」にあたる部分が左に詰められて分割表示されています。
最後の引数 [埋めるセル値] は省略なので、不足部分は #N/A で埋められています。

[一致モード] に 1 を指定した場合

5番目の引数 [一致モード] で大文字と小文字を区別するかどうかを指定できます。

[一致モード] に 1 を指定した場合

・大文字と小文字を区別しません。
・A1セルは、列の部分「C」は大文字、行の部分「r」は小文字となっています。
・対して、列区切りは “C” 大文字、行区切りも “R” 大文字としています。
・行の部分が大文字”R”と小文字”r”で相違していますが、そのまま分割表示されました。

[一致モード] が 0 (規定値)の場合

[一致モード] が 0 (規定値)の場合

・大文字と小文字を区別して検証します。
・前の例と同様の設定で、行の部分が大文字”R”と小文字”r”で相違しています
・大文字と小文字が区別して検証された結果、”r” は無視されて表示されました。

最後の引数 [埋めるセル値] の動作

最後の引数 [埋めるセル値] を省略した場合は、不足部分は #N/A で埋められます。

引数 [埋めるセル値] に何も指定しない場合
引数 [埋めるセル値] に何も指定しない場合

引数を省略したのとは違います。引数の値を指定しない場合です。
・”,” を入力して引数をセットしていますが値の部分は空白の状態です。
この場合は、ご覧の通りセルには「0」として表示されます。

引数 [埋めるセル値] に文字列を指定
引数 [埋めるセル値] に文字列を指定

引数に値を指定すれば詰められて空いセルには「指定した値」を表示できます。
・画像のとおり値に “Blank” と指定したので、セルに「Blank」と表示できました。

まとめ(おわりに)

以上、TEXTSPLIT 関数 について解説しました。

くるみこ
くるみこ

TEXTSPLIT関数を使えば、住所氏名データを空白で分割したり、CSVデータの分割などが簡単にできます。引数指定方法などをいろいろなサンプルでテストしておきましょう。

分割されたデータは、スピルされてセルに表示されるのがよくわかりました。

でも、引数 [空を無視] をTRUE で無視に指定するケースってあるんでしょうか?
いろいろなデータでもっと試してみます(^^♪

★★★ ブログランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

スポンサーリンク
スポンサーリンク

過去記事のサンプルファイルをダウンロードできます

この記事で使用したサンプルの登録はありません。
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください