いまさら聞けないExcelの使い方講座

【Excel】文字列の分割が面倒？ 一気に解決できる3つの関数を使いこなそう

  • 今井 孝

2024年12月4日 06:55

「AB-1234-UK」から「1234」をサッと取り出せるTEXTBEFORE/TEXTAFTER関数

文字列の分割が従来よりも簡単に

　今回は、入力済みの文字列データを分割する際に便利な新関数を3つ紹介します。2022年からMicrosoft 365のExcelで利用できるTEXTBEFORE/TEXTAFTER/TEXTSPLIT関数です。

　文字列の分割は、従来の関数でもできているので、あまり目新しい感じはしませんが、例えば、以下のようなコードから「-」に挟まれた“真ん中”の文字列だけを取り出すことを考えてみましょう。「-」の前後の文字数がバラバラなので、FIND関数を使って「-」の位置を探して、MID関数の引数に指定するのが定番でした。

あるコードから「-」に挟まれた文字（ここでは数値）を取り出す場合、FIND関数とMID関数を組み合わせて、「=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)」のような数式を入力します。

　処理の内容は単純ですが、FIND関数を何度も入力する必要があり、複雑なネストに混乱しますよね。一発で記述できる人は少ないと思います。これを一気に解決できるのが、TEXTBEFORE/TEXTAFTER/TEXTSPLIT関数です。

TEXTBEFORE関数とTEXTAFTER関数

　TEXTBEFORE/TEXTAFTER関数は、指定したテキスト（区切り記号）の前・後を取り出します。構文は以下の通りです。引数は6つありますが、最初の3つだけ指定すれば事足ります。［見つからない時］は「#N/A」エラーを出したくない場合に指定してください。

TEXTBEFORE/TEXTAFTER関数の構文。TEXTBEFORE関数は［文字列］の［区切り文字］よりも前、TEXTAFTER関数は後ろの文字列を返します。複数の［区切り文字］が含まれる場合は［区切り位置］に数値を指定できます

　使い方は簡単です。上記のサンプルで最初の「-」より前を取り出すなら、「=TEXTBEFORE(A2,"-")」のように指定します。3つめの引数［区切り位置］を省略した場合は「1」と見なされて、1つめの［区切り文字］より前が取り出されます。TEXTAFTER関数の使い方も同様です。

セルB2に「=TEXTBEFORE(A2,"-")」と入力しました（①）。3つめの引数［区切り位置］を省略しているので「1」と見なされて、1つめの「-」より前が取り出されています
2つめの「-」より前を取り出す場合は［区切り位置］に「2」と指定します（②）
セルD2に「=TEXTAFTER(A2,"-")」と入力しました（③）。3つめの引数［区切り位置］を省略しているので「1」と見なされて、1つめの「-」より後ろが取り出されています
2つめの「-」より後ろを取り出す場合は［区切り位置］に「2」と指定します（④）

　なお、区切り文字として「-」と「/」が混在している場合は「=TEXTBEFORE(A2,{"-","/"})」のように配列の形式で指定します。

A列のコードに「-」と「/」が混在している例です。「=TEXTBEFORE(A2,{"-","/"})」のように指定すると（⑤）、「-」または「/」という意味になります

“真ん中”を取り出す

　冒頭の例にあった「-」に囲まれた文字列を取り出してみましょう。TEXTBEFORE関数とTEXTAFTER関数を組み合わせるだけです。ネストの内側にTEXTAFTER関数、外側にTEXTBEFORE関数を指定することがポイントです。逆にすると「#N/A」エラーが表示されてしまいます。

セルB2に「=TEXTBEFORE(TEXTAFTER(A2,"-"),"-")」と入力しました（⑥）。内側のTEXTAFTER関数で［区切り文字］より後ろを取り出したうえで、外側のTEXTBEFORE関数で、前を取り出しています

TEXTSPLIT関数でまとめて分割する

　TEXTSPLIT関数を使うと、［区切り文字］で文字列を一気に分割できます。結果はスピルで表示されるので、入力する数式は1つのみです。“真ん中”が欲しいなら結果をコピペしてもいいですね。

　なお、TEXTSPLIT関数もTEXTBEFORE/TEXTAFTER関数と同様に、「{"-","/"})」のような配列の形式を利用することもできます。詳しくはこちらの記事も参照してください。

セルB2に「=TEXTSPLIT(A2,"-")」と入力しました（⑦）。結果はスピルで表示されるので、C列の結果をコピペすれば、“真ん中”を簡単に取り出せます

複雑なネストは必要なし

　TEXTBEFORE/TEXTAFTER/TEXTSPLIT関数は、従来のFIND/MID関数のネストよりも簡単ですよね。本稿執筆時点では、Microsoft 365のExcelのみの扱いですが、使える環境にある人はかなり時短効果があるのではないでしょうか。