Googleスプレッドシートでリンクを取り出す

質問

Googleスプレッドシートで、例えばA1セルに
=HYPERLINK("http://www.yahoo.co.jp","ヤフー")
と入力してあります。

A2にURLだけ取り出すことはできるでしょうか？
平凡に「http://www.yahoo.co.jp」
とURLだけ表示されるようにしたいのです。

数百業もデータを作ってしまった後に「URLだけ欲しい」と思ってしまったので、手動で取り出すのが大変で、アドバイスを頂けますと幸いです。

回答No.2

imogasi

#1です。＃１ではＶＢＡが中心の回答になり、けなされてしまったが、続いて考えてみました。
こちらもエクセルでの回答すが、こちらは関数でやって見ました。
エクセルでは関数だけで、できそうだということです。
「Googleスプレッドシート」では、「ＦＯＲＭＵＴＥＸＴ関数」が今のところ無いようなのですが、（よければ質問者の方で存在するか調べてください）残念です。
エクセルの回答なので、質問者にはこれでは回答として不満でしょう。
今後に、質問者以外の、他の人にも目に留まることも考えて、上げてみます。（私は、ほぼいつも質問者以外の方も、後日読んでくれることを意識して回答してます。）
検索が充実したおかげで、今では（２０１５年）ＷＥＢでの過去質問の疑問点の解説によく出くわします。この質問と同じ質問をするひと（ただしエクセルで）がいて、ＷＥＢ照会したその際に、その人のお役に立てばと思う。
ーーー
Ｂ列は関数を入れた結果の見た目です。
例　　Ｂ列　　　Ｃ列
ヤフー =HYPERLINK("http://www.yahoo.co.jp","ヤフー")

http://www.yahoo.co.jp

グーグル =HYPERLINK("http://www.google.co.jp","グーグル")

http://www.google.co.jp

大塚 =HYPERLINK("https://faq.qqweb.jp/faq/viewer.asp?ID=AC9A687811","大塚")

https://faq.qqweb.jp/faq/viewer.asp?ID=AC9A687811

ヨドバシ =HYPERLINK("http://www.yodobashi.com/","ヨドバシ")

http://www.yodobashi.com/
＜Ｂ列の式＞
Ｂ２に （ハイパーリンクの設定）
=HYPERLINK("http://www.yahoo.co.jp","ヤフー")
＜Ｃ列の上の方の式＞
Ｃ２に　（ハイパーリンク設定式を別セルに文字列表示化）
=FORMULATEXT(B2)
＜Ｃ列の下の方の式＞
Ｃ４に　（文字列からアドレス部分を抜出し）
=MID(C2,13,FIND(",",C2)-14)
ーーー
グーグル以下の例の式の解説は、同様なので省略。

お礼

bn9

回答No.1

imogasi

（１）質問者は、「Googleスプレッドシート」での解法を望んでいるのは、わかりますが
（２）参考までにエクセルでは、過去に同じような質問があるが、
　　（Ａ）ワークシート関数（普通にエクセル関数といっている関数）での解法は存在しないようだ、
　　（Ｂ）ＶＢＡなら、WEBに下記のような回答があり、
Function GetLinkAdr(Rng As Range) As String
Dim StrAdr As String
Application.Volatile
With Rng.Cells(1)
If .HasFormula Then
If InStr(.Formula, "=HYPERLINK") = 1 Then _
StrAdr = Split(.Formula, Chr(34))(1)
Else
If .Hyperlinks.Count > 0 Then _
StrAdr = .Hyperlinks(1).Address
End If
End With
GetLinkAdr = StrAdr
End Function
上記VBA関数（Function)
Sub test02()
Range("c2") = GetLinkAdr(Range("b2"))
Range("c7") = GetLinkAdr(Range("b7"))
End Sub
でできることを確認した。
ーー
ただし
（ｘ）HYPERLINK関数での設定したセルと
（Y)VBAで設定したしたセルと、捉え方に差があるようでややこしい（十分自信なし）
それが
If .HasFormula Then
・・
Else
・・・
EndIf
と聞いて２つの部分（Else以下とその上の部分）に分けている理由のようだ。
（X)のコードは、１セルの例で
Sub test01()
Range("B3").Hyperlinks.Add Anchor:=Cells(3, 2), Address:="http://www.yahoo.co.jp"
End Sub
（Y)で設定すると
Public Sub GetURL()
Dim h As Hyperlink
MsgBox ActiveSheet.Hyperlinks.Count
For Each h In ActiveSheet.Hyperlinks
MsgBox h.Address
h.Range.Offset(0, 1) = h.Address
Next
End Sub
では引っかかる（取り出せる）。しかし（X)で設定したものは捉えられないようだ。
ーーー
質問者はエクセルVBAは経験ないだろうが、Googleスプレッドシートのマクロ（Google Apps Scriptプログラミング）も経験ないだろうから、なお勉強しにくい。
この後の回答か、自分でWEBなりを調べて関数での回答を期待して、ください。
（望み薄と思うが）
エクセルとの互換性やエクセルが表計算ソフトで先行開発している（特にVBA・マクロ）ので、まずGoogleスプレッドシートにも関数がないのではないか。またマクロの記事でも調べにくいのでは。
もしクセルにデータを移して、VBAでやるのであれば、
ある列の、連続した「数百行ものデータ」をもとに、隣列にでも、アドレスの文字列を出すことはできる。ただし上記の　２．（B)のプログラムを多少修正する必要がある。

お礼

bkk