FIND/SUBSTITUTEなど関数や複雑すぎてわからない…
そもそも「-」の前や後ろの文字を、Excel関数で取得する方法がわからない…
「kks – U8 – pQZJjR」のように「-」で区切られた文字に対して、
「-」の前の文字だけ取得した！
「-」の間の文字だけ欲しい！
「-」の後ろの文字だけ出力したい！ なんて場面ありませんか？
少し詳しい方なら、FIND・SUBSTITUTEなどでできると知っている人もいるでしょう。ただそのような人であっても、コードが複雑でわかりにくく、関数を作るのがめんどくさいと思う内容だと思います。
今回はそう言った問題を、ChatGPT×Excel関数で解決できる方法を紹介します。
FIND関数に焦点を当て、基本的な使い方やコード解説、SUBSTITUTE関数との組み合わせた使い方なども紹介しますので、ぜひ参考にしてみてください。
また今回は関数ですが、ほかのページではChatGPTを使ったVBA作成法も紹介しています。良ければ、そちらも見てみてください。
1 FIND関数の右から・後ろから検索はChatGPTで簡単解決！
1-1 サンプル
以下のように、A列に「-」区切りの型式が並んでいて、B～D列に「-」の前、間、後ろを書き込みたい場合を想定します。
ここから行うこととしては、ChatGPTで関数を作成する→Excelに書き込むという2ステップだけです。
1-2 【プロンプト例】ChatGPTで関数を作成する
ChatGPTに指示する言葉のことを、プロンプトと呼びます。コードを作成する際は、どのようなプロンプトを使うかが重要となってきます。
今回の場合、以下のようなプロンプトを活用すると、スムーズにコードを生成できます。
プロンプト例
型式
kks-U8-pQZJjR
pJ-UjG3sy68-g4
u-g5niEtJid-3s
hNtt-6GzfVr-73
9i-M7gcZ9bQ-du
上記のような型式がA2セルから並んでいる場合、次の関数を作成してください
①「-」より前の文字列だけを抽出する関数をB列に
②初めの「-」と後の「-」の間にある文字列だけを抽出する関数をC列に
③右から数えて初めにある「-」の、後ろにある文字列だけを抽出する関数をD列に
このような場合は、まず関数で変換したい列の一部をそのままChatGPTに貼り付けます。
今回の場合、「-」の場所がバラバラです。それをChatGPTに理解させるよう、5～10程度サンプルを張り付けましょう。
そして、どういった関数を作ってほしいかを文字で記載します。できるだけ詳しく、どういったことがしたいのかを書いていきます。
この時のポイントは、位置を指示することです。どの場所のセルを対象に、どこに出力するかを書くようにしましょう。これを行うことで、作成された関数を修正せず、そのまま貼り付けて使えます。
今回は、A2から「-」区切りの型式が並んでいて、B～D列に「-」の前、間、後ろを書き込みたいので、場所をしっかりと明記します。
出力結果（関数部分抜粋）
先ほどのプロンプトを実行すると、関数が出力されます。この内容は、このままコピペでも使用可能です。
①「-」より前の文字列だけを抽出する関数をB列に
=LEFT(A2,FIND("-",A2)-1)
②初めの「-」と後の「-」の間にある文字列だけを抽出する関数をC列に
=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)
③右から数えて初めにある「-」の、後ろにある文字列だけを抽出する関数をD列に
=RIGHT(A2, LEN(A2) - FIND("~", SUBSTITUTE(A2, "-", "~", LEN(A2) - LEN(SUBSTITUTE(A2, "-", "")))) )
後はコードを2行目に貼り付け、
オートフィルを行えばリストが完成します。
ここからは今回使った関数の解説として、FIND関数やほかの関数との組み合わせについて解説していきます。
ChatGPTを使ってFIND関数やSUBSTITUTE関数を使用する場合、このプロンプト書き方と貼り付けくらいができれば問題ありませんが、知っておくと修正が楽になるので、ここからもぜひ参考にしてみてください。
2 FIND関数の基本構文と使い方
そもそもFIND関数は、文字列の中から特定の文字の場所を数字で返す関数です。左から数えて特定の文字が何番目にあるかを出力してくれます。
基本の構文は以下の通りです。
=FIND(検索文字列, 対象, [開始位置])
例えば、サンプルのA2セル「kks-U8-pQZJjR」という文字列で最初の「-」の位置を調べる場合、以下のように記載し、結果は「4」となります。
=FIND("-", A2) '結果:4
3 FIND関数の応用例
3-1 LEFT/MID/RIGHT関数との組み合わせ
FIND関数とLEFT、MID、RIGHT関数を組み合わせることで、さらに複雑な文字列の操作が可能になります。
まずそれぞれの基本的な使い方ですが、
LEFT関数は、文字列の先頭(左)から指定した文字数を抽出
MID関数は、文字列の指定した位置から指定した文字数(間)を抽出
RIGHT関数は、文字列の末尾(右)から指定した文字数を抽出する関数となっています。
=LEFT("Excelで効率化", 5) '結果: Excel
=MID("Excelで効率化", 6, 1) '結果: で
=RIGHT("Excelで効率化", 3) '結果: 効率化今回ChatGPTで作成した関数も、これら3つとFINDやSUBSTITUTEと組み合わせて関数を作成しています。とても簡単な関数ではありますが、これらの関数を組み合わせることで、特定の位置に基づいた文字列を抽出できます。
3-2 FIND関数＋LEN関数
FIND関数とLEN関数を組み合わせることで、文字列の一部を抽出できます。LEN関数は文字全体の長さを返すので、FIND関数で位置を特定し、その位置を基にLEN関数で範囲を指定して抽出することができます。
例えば、文字列の一部を取り出したい場合に次のように使います
=RIGHT(A2, LEN(A2) - FIND("-", A2))
この式では、A1セルの文字列から最初の「-」以降の文字列を抽出します。
SUBSTITUTE関数を活用した右側検索
ここからかなり複雑になってきます。SUBSTITUTE関数は、サンプルで右側にある「-」の位置取りに使用している関数になります。
そもそもSUBSTITUTE関数は、文字列内の指定した文字を別の文字に置き換える関数です。
=SUBSTITUTE("Excelで効率化", "効率化", "最適化") '結果: Excelで最適化上の例では単純な置き換えを行っていますが、第四引数で数字を指定すると「〇番目の文字だけを変換する」ことができます。先ほどのサンプルで右側からの検索のときに、この機能を使っています。
=RIGHT(A2, LEN(A2) - FIND("~", SUBSTITUTE(A2, "-", "~", LEN(A2) - LEN(SUBSTITUTE(A2, "-", "")))) )この式では、SUBSTITUTE関数を使用して、最後のハイフンを特定し、その位置以降の文字列を抽出しています。いくつも関数が入って複雑に見えますが、流れとしては以下となります。
①文字列中の「-」の数を数える
LEN(A2) - LEN(SUBSTITUTE(A2, "-", ""))「-」を削除した後の文字数差で、何個「-」があるかを計算します。
②最後の「-」を「~」に置き換える
SUBSTITUTE(A2, "-", "~", ①)最後の「-」だけを「~」に変えます。最後の場所は①で数えた「-」の数を入れて求めます。
③「~」の位置を取得する
FIND("~", ② ①)最後の「-」がどこにあるかを調べます。先ほど「-」を「~」に変換したので、「~」の位置を取得することで、最後の「-」の位置がわかります。
④右側の文字列を抽出
RIGHT(A2, ③ ② ①)最後の「-」以降の文字列を取り出します。RIGHT関数は、文字列の末尾(右)から指定した文字数を抽出する関数なので、①～③で求めた「~」の位置をもとに、右側の文字列を抽出しています。
ChatGPTで作成された関数は、この流れで動作しています。
3-4 FIND関数とSEARCH関数
FIND関数とSEARCH関数は似ていますが、SEARCH関数は大文字と小文字を区別しないという特徴があります。
例えば、A2に「I love Excel」と入力されている場合、FINDで”excel”を探すとエラーとなります。
=FIND("excel", A2) 出力: #VALUE!ただSERCHで”excel”を探すと8が出力されます。
=SEARCH("excel", A2) 出力: 8このように、大文字・小文字を区別せずに文字列を検索したい場合は、FIND関数の代わりにSEARCH関数を使うと便利です。
4 まとめ
FIND関数は文字列の抽出にはとても便利ですが、複雑になりやすい関数でもあります。
そういったデメリットを、ChatGPTを活用することで克服することができるため、これまで以上に手軽に業務効率化が可能になります。Excelの複雑な文字列操作は、ChatGPTで簡単に解決できることを覚えておきましょう。
またChatGPT×VBAを行うと、関数の自動作成以上に効率化を図ることができます。このサイトではChatGPTを使ってVBAを作成する方法を、VBA初期設定レベルから解説しています。ぜひそちらも参考に、効率化を進めていってください。
コメント