Excelでデータを受け取ったとき、1つのセルに複数の情報がまとめて入っていることがあります。
たとえば、次のような長い文字列です。
Product ID: 776, Product Qty: 1, Customer Name: Fake Name, Your employee number: P57991, Start Date: 2026/09/10
この中から
「Customer Name → Fake Name」
「Your employee number → P57991」
のように、特定の項目だけ別セルへ抽出したいことがあります。
1件ずつコピーすることもできますが、データが何十行もあると大変です。
ですが、Excelでは、TEXTAFTER関数とTEXTBEFORE関数を組み合わせることで、長い文字列から特定の文字や項目だけを自動で取り出せます。
この記事では、「項目名: 値」の形式で保存された文字列から、必要な部分だけを抽出する方法を解説します。
Excelで文字列から特定の項目を抽出する方法
今回はA2セルに長い文字列が入力されているものとして、「Customer Name」の値である「Fake Name」だけを取り出します。
「Fake Name」だけを取り出すには、以下のように数式を入力します。
=TRIM(TEXTBEFORE(TEXTAFTER(A2,"Customer Name:"),","))この数式の結果は「Fake Name」になります。
数式の仕組み
先ほどの数式の意味を解説します。
①まずTEXTAFTER関数で「Customer Name:」より後ろを取り出します。
→これで、「Customer Name:」以降の文字列を抜き出すことができます。
②次にTEXTBEFORE関数で最初のカンマより前だけを取り出します。
→これで、最初のカンマ前にある「 Fake Name」を抜き出すことができます。
ですが、このままだと前に余分な空白が入っている状態になります。そこで、
③最後にTRIM関数で前後の余分なスペースを削除します。
→これで余分な空白を削除することができます。
つまり、「Customer Name:」の後ろから、次のカンマまでを取り出す考え方です。
※この方法は、各項目がカンマで区切られているデータを想定しています。取り出したい値そのものにカンマが含まれている場合は、途中までしか抽出されないことがあります。
社員番号など別の項目も抽出できる
「Your employee number: P57991」から社員番号だけを取り出す場合は、次の数式を使います。
=TRIM(TEXTBEFORE(TEXTAFTER(A2,"Your employee number:"),","))結果は「P57991」になります。
この方法はCustomer Name、employee number、Product ID、Product Qtyなど、後ろにカンマが続く項目へ利用できます。
抽出したい項目に合わせてTEXTAFTER関数内の項目名を変更してください。
Start Dateのように文字列の末尾にある項目は、後ろにカンマがありません。
末尾項目にも対応するには、TEXTAFTERの結果へカンマを付けてから切り出します。
=TRIM(TEXTBEFORE(TEXTAFTER(A2,"Start Date:")&",",","))
項目名をセルに入力して複数項目を抽出する方法
抽出項目が多い場合は、数式へ毎回項目名を書くより、見出しセルを利用すると便利です。
B1に「Customer Name」、C1に「Your employee number」と入力し、B2へ次の数式を入力します。
=TRIM(TEXTBEFORE(TEXTAFTER($A2,B$1&":"),","))右方向へコピーすると、C1の項目名を使って社員番号も抽出できます。
さらに下方向へコピーすれば、A列に長い文字列が何十行あってもまとめて処理できます。
項目がない場合のエラーを空白にする
目的の項目が存在しない行ではエラーになります。
IFERROR関数を追加すると、項目が見つからない場合に空白を表示できます。
=IFERROR(TRIM(TEXTBEFORE(TEXTAFTER(A2,"Customer Name:"),",")),"")大量のデータを処理する場合は、この数式を使っておくと扱いやすくなります。
TEXTAFTER・TEXTBEFOREが使えない場合
TEXTAFTER・TEXTBEFOREはMicrosoft 365やExcel 2024で利用できます。
Excel 2021・2019・2016などで使えない場合は、MID関数やSEARCH関数を組み合わせても抽出できます。
=TRIM(MID(A2,SEARCH("Customer Name:",A2)+LEN("Customer Name:"),SEARCH(",",A2,SEARCH("Customer Name:",A2))-SEARCH("Customer Name:",A2)-LEN("Customer Name:")))
ただし数式がかなり長くなります。
TEXTAFTER・TEXTBEFOREが利用できる場合は、そちらのほうが短く、あとから見直しやすいでしょう。
文字列全体を分割したい場合はTEXTSPLIT関数も便利
今回の方法は、長い文字列から特定の項目だけを取り出したい場合に向いています。
一方、「商品番号,商品名,数量,金額」のような文字列をカンマなどでまとめて別セルへ分割したい場合は、TEXTSPLIT関数が便利です。
👉TEXTSPLITについては、以下記事で詳しく解説しています。
文字列から数字だけを取り出したい場合
今回の方法は「項目名の後ろにある値」を抽出する方法です。
「商品番号ABC12345」から「12345」だけを取り出すような場合は、数字抽出の方法を使います。
👉数字だけを抽出したい場合は、REGEXEXTRACT関数を使うと便利です。
以下の記事で詳しく解説していますので、ぜひチェックしてみてください。
まとめ｜項目名の後ろから必要な文字だけ抽出できる
Excelで1つのセルに大量の情報がまとめて入力されていても、TEXTAFTER関数とTEXTBEFORE関数を組み合わせれば、必要な項目だけを別セルへ抽出できます。
=TRIM(TEXTBEFORE(TEXTAFTER(A2,"Customer Name:"),","))「項目名の後ろ」から「次のカンマより前」へ文字列を絞り込みます。
名前・社員番号・商品番号など、毎回同じ項目を手作業でコピーしている場合は、数式を一度作って下までコピーしておくと効率的です。
ぜひ業務の中で活用してみてください。
コメント