TEXTBEFORE関数(テキストの指定文字列より前の部分を返す)
TEXTBEFORE関数はOffice365のExcelに2022年になってから追加された新しい関数です。
区切り文字列の前ではなく後ろを取得する場合はTEXTAFTER関数を使用します。
TEXTBEFORE関数の構文
text テキスト |
対象のテキスト文字列。 必ず指定します。 |
delimiter 区切り文字列 |
区切りに使用する文字。 textをdelimiterに指定された文字で区切って前の部分を返します。 必ず指定します。 |
instance_num 区切り位置 |
行の区切りに使用する文字。 区切り文字列が複数存在している場合の何番目を対象とするかの数値を指定します。 正の数を指定するとテキストの先頭から検索を開始し、負の数を指定するとテキストの末尾から検索を開始します。 省略可能です。 既定値は 1 です。(先頭から検索し最初に出現した区切り文字位置) |
match_mode 検索方法 |
区切り文字の検索時に、英文字の大文字小文字を区別するかどうかを指定します。 0:大文字と文字を区別する 1:大文字と小文字を区別しない 既定値は 0:(大文字と文字を区別する) |
match_end 終端と一致 |
テキストの末尾を区切り記号として扱うかどうか。 0:一致して終了しない 1:一致して終了 省略可能です。 既定値は 0:(0:一致して終了しない) これは非情に分かりづらいので、この下で詳しく解説します。 |
if_not_found 見つからない時 |
一致するものが見つからない場合に返される値。 省略可能です。 既定値は #N/A です。 |
テキスト(text)
前から検索 (instance_num が正) には空のテキストが返され、末尾から検索するとき (instance_num が負の場合) にはテキスト全体が返されます。
区切り文字列として","を指定した場合
正の数値を指定した場合は、先頭から1,2,...となります。
負の数値を指定した場合は、末尾から-1,-2,...となります。
TEXTBEFORE関数は、この区切り文字の位置より前の部分の文字列を返します。
instance_numの絶対値が、存在する区切り文字列の数の絶対値より大きい場合は #N/A! エラーになります。
(区切り文字列が2つ存在する場合なら、3以上または-3以下の指定は #N/A)
instance_numに0を指定すると #VALUE! エラーになります。
省略時の既定値は 1 です。
区切り文字は記号を指定することが多いので、区切り文字として英文字を使う機会は多くないと思います。
(区切り位置が正数ならテキストの最後、区切り位置が負数ならテキストの最初)
match_endに1を指定した場合は検索において終端も一致していると判定します。
正の数値を指定した場合は、先頭から検索していきテキストの末尾も一致として扱います。
負の数値を指定した場合は、末尾から検索していきテキストの先頭も一致として扱います。
上記の場合なら、区切り位置(instance_num)に3または-3を指定した場合は空文字""が返されます。
テキストの末尾が1、テキストの先頭が-1になります。
区切り位置を省略した場合は1指定になるので、空文字""が返されることになります。
省略時の既定値は #N/A です。
テキストがエラー値の場合はこの指定は有効とは成らず、TEXTBEFORE関数は同じエラーを返します。
TEXTBEFORE関数の使用例と解説
区切り文字列(delimiter)だけを指定
=TEXTBEFORE(A1,",")
省略された引数は以下の既定値になります。
検索方法(match_mode) = 0:大文字と文字を区別する
終端と一致(match_end) = 0:一致して終了しない
見つからない時(if_not_found) = #N/A
=TEXTBEFORE(A1,";")
テキスト(text)にセル範囲(複数セル)を指定
=TEXTBEFORE(A1:A2,",")
テキストが横範囲なら横に、縦範囲なら縦にスピルします。
区切り位置(instance_num)に正の値を指定
=TEXTBEFORE(A1,",",2)
=TEXTBEFORE(A1,",",1) → 「Excel」
=TEXTBEFORE(A1,",",2) → 「Excel,Word」
instance_numに3以上を指定した場合は #N/A エラーとなります。
区切り位置(instance_num)に負の値を指定
=TEXTBEFORE(A1,",",-1)
=TEXTBEFORE(A1,",",-1) → 「Excel,Word」
=TEXTBEFORE(A1,",",-2) → 「Excel」
instance_numに-3以下を指定した場合は #N/A エラーとなります。
検索方法(match_mode)に「1:大文字と小文字を区別しない」を指定
=TEXTBEFORE(A1,"A",,1)
したがって、先に一致した"a"より前の文字列が返されています。
=TEXTBEFORE(A1,"A",,0) → 「123a45」
=TEXTBEFORE(A1,"A",,1) → 「123」
終端と一致(match_end)に「1:一致して終了」を指定
=TEXTBEFORE(A1,",",3,,1)
つまりテキストが、「Excel,Word,Access,」である場合と同様の結果になります。
省略または「0:一致して終了しない」の指定の場合は、検索文字列は2つしかないので3の指定では #N\A になります。
=TEXTBEFORE(A1,",",-3,,1)
終端と一致(match_end)に「1:一致して終了」を指定しているので、文字列の先頭に区切り文字列があるものとして動作します。
つまりテキストが、「,Excel,Word,Access」である場合と同様の結果になります。
省略または「0:一致して終了しない」の指定の場合は、検索文字列は2つしかないので-3の指定では #N\A になります。
実際に存在する区切り文字列の数より多い数を指定している可能性がある場合に、
テキストをそのまま返したければ、
=TEXTBEFORE(A1,",",3,,0,A1)
空文字列を返したければ、
=TEXTBEFORE(A1,",",3,,0,"")
このように指定したほうが数式が分かり易いと思います。
見つからない時(if_not_found)を指定
=TEXTBEFORE(A1,";",,,,A1)
上記では、存在しない場合にテキスト文字列をそのまま出力する指定にしています。
空文字列の""を指定すれば出力セルは空白になります。
区切り文字列(delimiter)に配列を指定
=TEXTBEFORE(A1,{",",";"})
配列の先頭文字列だけが有効になるようです。
これは少し不満のある仕様ですが、仕方ありません。
区切り文字列(instance_num)に配列を指定
=TEXTBEFORE(A1,",",{1,2})
縦配列の {1;2} と指定すれば縦にスピルします。
TEXTBEFOREの応用例:TEXTBEFOREまたはTEXTAFTERとの組み合わせ
LAMBDA以降の新関数の問題集 ・・・ 解答は別ページになっています。
同じテーマ「エクセル入門」の記事
WRAPROWS関数(1次元配列を指定数の行で折り返す)
WRAPCOLS関数(1次元配列を指定数の列で折り返す)
TEXTSPLIT関数(列と行の区切り記号で文字列を分割)
TEXTBEFORE関数(テキストの指定文字列より前の部分を返す)
TEXTAFTER関数(テキストの指定文字列より後ろの部分を返す)
LAMBDA以降の新関数の使用例
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)
直積(クロス結合、交差結合)とピボット解除
複数の文字列を検索して置換するSUBSTITUTE
LAMBDA以降の新関数の問題集
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.セルのクリア(Clear,ClearContents)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.条件分岐(Select Case)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。