Microsoft Excelでは、区切り文字列内の文字を解析または検索することが一般的なタスクです。Excelには便利な文字列関数がいくつか用意されていますが、結果として得られる式は複雑です。Power Queryでは複雑な式は不要で、Flash Fillはパターン検索に優れていますが、Excelに新たに2つのテキスト関数が追加されたことで、この作業はさらに簡素化されます。
このチュートリアルでは、2つの新しいテキスト関数、TEXTAFTER()とTEXTBEFORE()について簡単に説明します。ここではTEXTAFTER()に焦点を当てますが、抽出する文字の方向を除き、ここで学ぶ内容はすべてTEXTBEFORE()にも当てはまります。
参照: Google Workspace vs. Microsoft 365: チェックリスト付き比較分析 (TechRepublic Premium)
Windows 10 64ビットシステムでMicrosoft 365を使用しています。Microsoftはこれらの機能を9月にリリースしたので、Web版Excelを含むすべてのMicrosoft 365サブスクライバーが利用できるはずです。
このチュートリアルの Microsoft Excel デモ ファイルをダウンロードできます。
確認するには、Excelブックを開き、セルに「=Text」と入力してください。図Aに示すように、オートコンプリート機能に新しい関数が表示されます。Microsoft 365をご利用なのに関数が表示されない場合は、管理者にお問い合わせください。
図A
文字列の途中にある文字を探すのは、文字列を扱う上で最も難しい作業の一つです。特定の文字を探し、すべての文字を数え、その文字の位置から文字数を引かなければなりません。もしこれまでこのような式を書いたことがないなら、幸運です。これからはもう書く必要はありません。
Excelの新しいTEXTAFTER()関数は、指定された文字または文字列の後に続く文字を返します。これにより、上記の面倒な作業は完全に解消されます。
TEXTAFTER() は次の構文を使用します。
=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
必要な引数は 2 つだけです。
text検索する文字列です。delimiter前後の文字を区切る特定の文字です。
残りの引数はオプションであり、結果を絞り込むのに役立ちます。
instance_numは、抽出するテキストの後ろにある区切り文字です。デフォルトでは、instance_num = 1負の数を指定すると、テキストの右から検索を開始します。このオプションの引数は、区切り文字が複数回出現する場合に使用します。match_modeテキスト検索で大文字と小文字を区別するかどうかを決定します。 0 はデフォルトで大文字と小文字を区別します。 1 は大文字と小文字を区別しません。match_endテキストの末尾を区切り文字として評価します。 0 はデフォルトで、テキストは完全に一致します。 1 は区切り文字をテキストの末尾に一致させます。if_not_found一致が見つからなかった場合に関数が返す値です。デフォルトは #N/A です。
区切り文字とは、文字列内の文字を区切る文字です。この用語に馴染みのない方は、最初の例で学ぶことができます。図Bに示すように、C3の関数は
=TEXTAFTER(B3,"/")
残りのセルにコピーすると、区切り文字として / 文字が指定されます。これは2つの文字列を除くすべての文字列で機能します。残りの2つの文字列には区切り文字が全く含まれていません。
図B
これらのエラー自体は問題ではありません。実際には、データの不整合が露呈しているだけなので、この場合はエラーを抑制する対策を講じることはお勧めしません。ただし、オプションの引数のいずれかを使用することで、エラーに関する有益な情報を表示できます。
図 C は、 if_not_found 関数を使用した結果を示しています。
=TEXTAFTER(B3,"/",,,,"Missing delimiter")
「区切り文字がありません」というメッセージは、エラー値よりも具体的で役立ちます。
図C
if_not_found。もう 1 つの重要な引数は instance_num です。これは、同じ区切り文字の複数のインスタンスが含まれる文字列を扱うことが多いためです。
図Dは、別のデータセットでこの引数がどのように機能するかを示しており、矛盾する値も明らかにしています。列Cは、比較のために最後の部分のみを返すパターンを見つけるためにFlash Fillを実行した結果を示しています。列DではTEXTAFTER()を使用しています。
=TEXTAFTER(B3," ",2,,,"Missing delimiter")
図D
値 2 は、関数に「区切り文字（スペース文字）」の2番目の出現以降の文字を返すように指示します。区切り文字は「」で表されます。引用符の間にはスペース文字があります。文字列に区切り文字が2つない場合は、「区切り文字がありません」というエラーが返されます。しかし、文字列の1つは「CEO」を返します。これは関数が検出できないエラーです。
次の関数を試してみてください:
=TEXTAFTER(B3," ",-1,,,"Missing delimiter")
列 E にあります。図 Eからわかるように、CEO インスタンスではまだ機能しません。ただし、驚くべきことに、E6 は何も返しません。
最初は値が単一の単語「Susan」だからだと思うかもしれませんが、次のセルの単語も「Harkins」なので、関数は予想どおり「Harkins」を返します。
図E
Susan または Harkins の末尾にスペース文字が含まれていると思われる場合、その通りです。Susan の末尾のスペース文字を削除すると、関数はエラーメッセージを返します。このような場合は、テキスト引数を TRIM() 関数で囲むことができます。
=TEXTAFTER(TRIM(B3)," ",-1,,,"Missing delimiter")
ExcelでTEXTBEFORE()を使用する方法
TEXTBEFORE() の説明に別の記事は必要ありません。この関数は、TEXTAFTER() と明白な違いを除いて同じです。TEXTBEFORE() は区切り文字の前の文字を返します。その他の引数、エラー、落とし穴はすべて同じです。
実際に動作を確認したい場合に備えて、ダウンロード可能なデモ ファイルには、TEXTBEFORE() を使用した同じデータが含まれています。
乞うご期待
もう1つ新しいテキスト関数、TEXTSPLIT() があります。これについては今後の記事で取り上げます。
