TEXTBEFORE関数は、元の文字列の「指定した文字または文字列」より前の部分にある文字列を返します。TEXTAFTER関数は後ろ側を返しましたが、TEXTBEFOREはその逆で前側を返します。
2022年8月下旬のアップデートで新たに一般公開された14個の文字列・検索/行列関数のひとつです。(3月から「Office Insider Program」でテストされていたものです)
はじめに
最新の関数は、サブスクリプション版「Microsoft 365」の最新「Excel for Microsoft 365」とWeb版で使用できます。
それでは今回は、新しく追加された3つの文字列を操作する関数のひとつ「TEXTBEFORE関数」について勉強しましょう。先に紹介した「TEXTAFTER関数」とは逆の動きをします。どんな関数なのか、基本的な使い方などを紹介ながら解説していきます。
「BEFORE」という名前だから「文字列」の「前側」ですよね。
よろしくお願いしますm(__)m
【この記事でわかることは】
・TEXTBEFORE 関数の使い方がわかります
TEXTBEFORE 関数の構文
TEXBEFORE 関数入力時に表示される「数式オートコンプリート」から確認します。
※ 対応していないExcelバージョンでは「オートコンプリート」にこの関数は表示されません。
※ リンクはこの関数の公式ヘルプの掲載場所です。
TEXTBEFORE 関数の構文と引数
表示される引数の表示画像がこちらです。TEXTAFTER関数の引数と同じですね。
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
引数が英文表記で分かりずらいので次のように整理しました。
引数 | 説明 |
---|---|
文字列 text | 対象とするテキスト文字列【必須】 |
区切り文字 delimiter | 区切り文字または文字列【必須】 |
[何番目] [instance_num] | 何番目の「区切り」が対象か指定 [省略=1] (-)負の値で指定すると後ろから何番目になる |
[一致モード] [match_mode] | 0 →【規定値】大文字と小文字を区別する 1 → 指定で大文字と小文字を区別しない |
[末尾一致] [match_end] | テキストの末尾を区切り文字として扱う 0 →【規定値】末尾を区切り文字としない (一致していない場合 #N/A を返す) 1 → 末尾を区切り文字とする |
[不一致の場合] [if_not_found] | 区切り文字が見つからない場合に返す値を 指定できる。省略すると #N/A が返る |
「引数」入力時(,)を打つと表示されるヒント(4番目と5番目の引数)が確認できる動画です。
※後ろのほうの表示が切れてしまったけど、感じだけわかってもらえれば(^^;
では、実際に動かしてみながら確認していきましょう。
TEXTBEFORE 関数の使用例
それでは引数を確認するため、いくつかの使用例を見ていきましょう。
必須な引数「文字列」と「区切り文字」だけ指定
TEXTBEFORE関数で必須な引数は、元の「文字列」と「区切り文字」の二つです。
「区切り文字」の指定方法は直接関数内に記述する方法文字列ありますが、次のようにセル参照で設定する使用例を使って見てみましょう。
・元の「文字列」を式内に直接記述してもOKなのですが、B2セルから参照するようにします。
・「区切り文字」も「文字列」と同様に、B3セルを参照します。
・TEXTBEFORE関数の式は、B4セルにセットします。
「区切り文字」を単一の文字で指定
文字列内に「区切り文字」が複数ある場合
デフォルトでは1番目の「区切り文字」
3番目の引数に [何番目] かを指定できる
2番目の「に」に指定した例です。
3番目の引数に(-)負数を指定した場合
・「区切り文字」が複数存在する場合、3番目の引数に「何番目」なのかを数値で指定します。
・指定数値に負数を使用すると後ろから「何番目」のように逆順で検索します。
・指定した数値が存在する「区切り文字」の数に合わない場合は #N/A エラーになります。
大文字と小文字を区別する
4番目の引数は検索するモードの設定です。
[一致モード]=0(区別するが省略時の規定値)
[一致モード]=1(区別しない)
・[一致モード] は大文字と小文字を区別するかどうかを指定できる引数です。
・何も指定しない場合の「規定値」は「0」を指定したのと同じ「区別する」です。
・「1」を指定すれば、大文字と小文字は「区別しない」設定になります。
文字列の末尾一致[match_end]とは
これが一番わかりにくい引数ですね。HELPやヒントを見てもよくわかりませんでした。
やはり、実際に動かしてみて確認するしかなかったです。
規定値(省略時)「0」の動作
・区切り文字は「EXCELVBA」で大文字と小文字を区別する設定の検索です。
・文字列の末尾まで検索した結果、該当がありませんでした。
・引数[末尾一致]が規定値の「0」なので「末尾を区切り文字としない」ので #N/A です。
「1」を指定した場合の動作
・引数[末尾一致]を「1」として「末尾を区切り文字とする」とした場合です。
・「末尾」の “” が区切り文字になり、末尾より前のすべての文字列が返りました。
最後の引数 [不一致の場合]
「区切り文字」が見つからなかった場合に返す値を指定することができます。
省略すると #N/A が返る場合に、この引数で指定した値を表示させることができます。
[不一致の場合] の文字列を指定した例
引数[末尾一致]=1 なら [不一致の場合] は不要
・上の画像をご覧になればわかると思いますが、[末尾一致] が「1」の場合は「区切り文字」が強制的に末尾にあてられます。(TEXTBEFOREの場合は末尾より前の文字列すべてが返ります)
・なので、[不一致の場合] を設定しても使われることはありません。
おわりに
以上、TEXTBEFORE 関数 について解説しました。
TEXTBEFORE関数を使えば、特定の文字を「区切り文字」に指定してそれより前の文字列だけにすることができます。引数の指定方法などをいろいろ試してみましょう。
どんなケースに使う場面があるのかも考えてみてください。
TEXTAFTER関数とあわせて、いろいろな設定で試してみようと思います(^^♪
★★★ ブログランキング参加中! クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
過去記事のサンプルファイルをダウンロードできます
この記事で使用したサンプルの登録はありません。
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください
・「区切り文字」に “/” 全角のスラッシュを指定しました。(半角ではエラーになります!)
・”/” は一つだけなので、その後ろにあるすべての文字列が表示されました。
【区切り文字を配列で複数設定できる】
・半角と全角のどちらでも対応したい場合は「区切り文字」をセル範囲(配列)で指定するか、手入力の場合は {“/”,”/”} のように指定します。そうしておけば、どちらかの文字が一致(TRUE)すればOKになります。