Excelから任意の文字列を抽出したい

質問

A列にリンクのタグがあるのですが、このような文字列から【リンク先】という部分と、【＜img src="" alt=""＞】の部分だけを取り出したいと思いますが、上手く出来ません。

A列に入っているタグは→＜a href="リンク先" target="blank"＞＜img src="" alt=""＞＜/a＞

A列にリンクのタグが入っています。
B列に【リンク先】
C列に【＜img src="" alt=""＞】
となるようにしたいです。

又A列の文字列には、＜img src="" alt=""＞が続いて2つか3つ入っているものもありますが、その場合は、D列・E列に順番に抽出したいです。

最大でも＜img src="" alt=""＞は4つまでしか入っておりません。

ダブルコーテーションの扱いなどがよく分からなくて、詳しかたいれば教えていただけるとありがたいです。

よろしくお願いいたします。

※【】はわかりやすくするためのものです。

回答No.6

shintaro-2 · ベストアンサー

＃１です

>結局どのようにB列とC列に数式を入れたかが知りたいのですが。
> 具体的に教えて頂けませんかね。

考え方さえ理解して戴ければ、後はやっていただけるものと思っていたのですがね、
どうせ掲載されたパターンはサンプルですから、
kurekurekun55 さんが実際にやる場合にも具体例を記載したとしてもコピペできるわけではありませんから同じことだと思いますよ。

A１が
<a href="リンク先A" target="blank"><img src="A" alt=""></a><img src="B" alt=""></a>
であれば

B1は
=MID(A1,FIND("""",A1)+1,FIND("""",A1,FIND("""",A1)+1)-FIND("""",A1)-1)
とすれば
リンク先A
を切り出せます。
findで探すものを”にせずとも、href=やtargetにしても良いです

例えば
C1は
=MID(A1,FIND("<",A1,2),FIND("<",A1,FIND("<",A1,2)+1)-FIND("<",A1,2))
とすれば、
<img src="A" alt="">
を切り出せます。

例えば
D1は
=MID(A1,FIND("/a><",A1)+4,FIND("/a>",A1,FIND("/a>",A1)+1)-FIND("/a><",A1)-6)
とすれば、
img src="B" alt=""
を切り出せます。
２つめのimgが無い場合のエラー対策は考えてください。

この方式だと、E,F列になるほど関数のネストが深くなりますので、
それを避けるためには作業列を作るのが後々楽です。

回答No.7

bunjii

>そして、改行なくした状態でやってみると <a href="" target="blank"> という感じで抽出されてしまいました。
当方で提示した数式はA1セルから >< の位置を検出しているだけであり、A1セルの内容は変化しませんので href="リンク先" だけがが href="" になる要素はありません。
当方では提示された文字列を対象にExcel 2013で検証しています。
Excel 2007以降のバージョンで再現できるはずです。
貼付画像はG列にもF列同様の数式を入力して <img src="" alt=""> が最大の4つまでについて再現したものです。
尚、セルの幅を小さくすると文字列全部が収まらないので行幅を大きくして折り返しの表示になっています。
G1=IFERROR(LEFT(SUBSTITUTE(A1,B1&C1&D1&E1&F1,""),FIND("><",SUBSTITUTE(A1,B1&C1&D1&E1&F1,""))),SUBSTITUTE(A1,B1&C1&D1&E1&F1,""))

回答No.5

shintaro-2

＃１です

"を探す場合は、［”]を[””]として変換し
さらに文字列をしめす["]と["]でくくって入力しますので
=find("""",a)　となります

>文字列は色々ありますので、何文字川わかりませんので、その設定を教えていただきたいです。

文字数が可変であれば
midで切り出す際に次の”<”の位置を探して差分をとれば、切り出す文字数はわかります。
次の”＜”でエラーが出れば、全体の長さを指定しておけば切り出しは可能です。

>また、ひとつのセルで処理できれば助かります。

当然、一つのセルで処理できますが？

> ＞データ->区切り位置　で区切り文字として　”＜”を指定します。
>そうすると、"<"が無くなりますが各列にデータを切り分けてくれますから楽です。

>もちろん分かりますが、文字列を貼り付けたらすぐに反映されるようにしておきたいのです。

すぐに反映とは、何を意図されています？

データ区切りで1回指定すれば、
そのシート（もしかしたらエクセル自体かも）を閉じない限り区切り文字は有効だったと思います。

補足

結局どのようにB列とC列に数式を入れたかが知りたいのですが。
具体的に教えて頂けませんかね。

区切りのやつは毎回データ区切りを、ファイルを開くたびにしなくてもいいということでしょうか？

データを貼り付けてきて、そのたびに区切りをしないと思っておりましたので、認識不足でした。

ただこの状態でも火口は必要なので、来切りを使った場合のやり方を知りたいです。

回答No.4

bunjii

> B1=LEFT(A1,FIND("><",A1)
>コレですと、すべて表示されてしまいます。
数式の最後の括弧閉じが1つ足りませんがタイプミスですか？
当方ではExcel 2013ですが正常に切り出せています。
念のためにA1セルの文字列を再確認してください。
<a href="リンク先" target="blank"><img src="" alt=""></a>
<a href="リンク先" target="blank"> <img src="" alt=""> </a>
下のように > < のように空白文字が中間に有ると位置の検出ができないので正しい切り出しができません。
FIND関数の検索文字を実際の文字列に合わせて変更する必要があるでしょう。

補足

追記ありがとうございます。

どうやら、おっしゃるように＞の後に改行が入っていたため上手く出来ていなかったようです。

そして、改行なくした状態でやってみると

<a href="" target="blank">

という感じで抽出されてしまいました。

回答No.3

bunjii

提示の文字列で実際の文字列と異なる部分が有るようです。
下記の1行にはタグの括りである<>が全角で書かれていますが実際のデータでは半角の<>でなければなりません。
＜a href="リンク先" target="blank"＞＜img src="" alt=""＞＜/a＞
　　　　　　　　↓
<a href="リンク先" target="blank"><img src="" alt=""></a>

A列の文字列が正しい書式で記述されていることを前提にすればタグの境目で区切れば良いことになり、B列は次の式で切り出せます。
B1=LEFT(A1,FIND("><",A1))
C列はA列の文字列からB列の文字列を削除したものから上記と同様に左側の文字列を切り出せば良いことになります。
C1=LEFT(SUBSTITUTE(A1,B1,""),FIND("><",SUBSTITUTE(A1,B1,"")))
D列はC列の切り出しと同様にしますがA列の文字列を前処理するときにB列とC列を削除した残りから切り出します。
D1=IFERROR(LEFT(SUBSTITUTE(A1,B1&C1,""),FIND("><",SUBSTITUTE(A1,B1&C1,""))),SUBSTITUTE(A1,B1&C1,""))
この時、FIND関数でエラーが起る可能性を考慮してIFERROR関数でエラー時の代替処理を行うようにします。
E列、F列までD列の手法を準用すれば次のようになります。
E1=IFERROR(LEFT(SUBSTITUTE(A1,B1&C1&D1,""),FIND("><",SUBSTITUTE(A1,B1&C1&D1,""))),SUBSTITUTE(A1,B1&C1&D1,""))
F1=IFERROR(LEFT(SUBSTITUTE(A1,B1&C1&D1&E1,""),FIND("><",SUBSTITUTE(A1,B1&C1&D1&E1,""))),SUBSTITUTE(A1,B1&C1&D1&E1,""))
B1からF1セルを同時に下へ必要数コピーすれば良いでしょう。

この数式はExcel 2007以降のバージョンで再現できるはずです。

補足

ご回答有難うございます。
＞＜a href="リンク先" target="blank"＞＜img src="" alt=""＞＜/a＞
　　　　　　　　↓
<a href="リンク先" target="blank"><img src="" alt=""></a>

ハイこれはここに貼るのにわざとそうしました。

＞A列の文字列が正しい書式で記述されていることを前提にすればタグの境目で区切れば良いことになり、B列は次の式で切り出せます。
B1=LEFT(A1,FIND("><",A1)

コレですと、すべて表示されてしまいます。

列でつまずくので、C列D列と全てエラーのなってしまいます。

回答No.2

keithin

sub macro1()
　dim h as range
　dim a as variant
　dim res as variant

　for each h in range("A1:A" & range("A65536").end(xlup).row)
　　a = split(replace(h.value, "><", ">\\\<"), "\\\")
　’リンク先
　　res = filter(a, "href")
　　h.offset(0, 1) = split(res(0), """")(1)
　’img src
　　res = filter(a, "img")
　　h.offset(0, 2).resize(1, ubound(res)+1) = application.transpose(res)
　next
end sub

＃エラー処理（a hrefやimg srcが含まれていない等）は無視してます。適切に応用してください。

補足

スミマセン、コレはVBAでやるってことですよね？

出来れば、関数のみで、エクセルに文字列を貼り付けたらすぐに反映されるようにしたいのです。

回答No.1

shintaro-2

>※【】はわかりやすくするためのものです。

方法１
【リンク先】という文字が固定であれば、いっそのことB列に”リンク先”と記入しておけばすみます。

【＜img src="" alt=""＞】については、
=find("<img",a1)　としておけば、一番左端の【＜img src="" alt=""＞】を見つけてくれますので、
そこからmid関数で必要な文字数を切り取ります。

C列以降は、B列で取得した<imgの場所がｋ文字目だとすると
=find("<img",a1,k+1) で2番目以降の【＜img src="" alt=""＞】を見つけてくれます。

D列以降も同様ですね

見つからなかった場合のif文を入れておくと良いでしょう。

方法２
データ->区切り位置　で区切り文字として　”＜”を指定します。
そうすると、"<"が無くなりますが各列にデータを切り分けてくれますから楽です。

補足

ご回答有難うございます。

＞【リンク先】という文字が固定であれば、いっそのことB列に”リンク先”と記入しておけばすみます。

コレに関しては固定であればわざわざ抽出する必要はありません。

＞【＜img src="" alt=""＞】については、
=find("<img",a1)　としておけば、一番左端の【＜img src="" alt=""＞】を見つけてくれますので、
そこからmid関数で必要な文字数を切り取ります。

文字列は色々ありますので、何文字川わかりませんので、その設定を教えていただきたいです。

また、ひとつのセルで処理できれば助かります。

＞データ->区切り位置　で区切り文字として　”＜”を指定します。
そうすると、"<"が無くなりますが各列にデータを切り分けてくれますから楽です。

もちろん分かりますが、文字列を貼り付けたらすぐに反映されるようにしておきたいのです。