ドメイン部分だけを抽出

質問

URLからドメイン部分だけ抽出する作業があります。
ですが、URL自体が数千個あるため、エクセルの数式で効率よくやりたいです。

http://〇〇.com/
http://〇〇.com/〇〇/
http://〇〇.〇〇.com/〇〇/
というように、A列のセルに一つずつURLが記入されています。

このURLから、「〇〇.com」だけを抽出したものを、
B列に表示するようにしたいです。

そして、「〇〇」のドメイン名だけをC列に表示。
「.com」の部分をD列に表示したいです。

エクセル２０１６です。
よろしくお願いいたします。

回答No.8

Chiquilin · ベストアンサー

#7です。
最初が「//」だったのを忘れてました。

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(A1,"/","</b><b>")&"</b></a>","//b[3]"),"")

ではどうでしょう。

補足

できました。ありがとうございます！

回答No.7

Chiquilin

Excel2016なら FILTERXML関数が使えるでしょう。

■B1
=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(A1,"/","</b><b>")&"</b></a>","//b[2]"),"")

■C1
=LEFT(B1,LEN(B1)-LEN(D1))

■D1
=TRIM(RIGHT(SUBSTITUTE(B1,".",REPT(" ",99)&"."),99))

補足

回答ありがとうございます！

シンプルで良いと思ったのですが、
■B1
=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(A1,"/","</b><b>")&"</b></a>","//b[2]"),"")

を記入しても、空白のままになります。

なにか他に、設定する点があるのでしょうか？

回答No.6

bunjii

>はい、http://のみです。
回答No.5の数式を一部簡略化できます。
=SUBSTITUTE(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",3)&"|")-1),LEFT(A1,FIND("://",A1)+2),"")
　　　　　　↓
=SUBSTITUTE(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",3)&"|")-1),"http://","")

>サブドメインを含めない形を抽出したいです。
「http://〇〇.△△.com/〇〇/」の場合は「〇〇.△△」ではなく「△△」と言うことでしたらドメインの区分を示すデータを網羅したデータベースが必要になります。
日本国内においても.ac.jp、.co.jp、.go.jp、.or.jp、.ad.jp、.ne.jp、.gr.jp、.ed.jpの他に一般地域型、地方公共団体の構成都道府県・政令指定都市、市町村・東京都特別区などに使われる都道府県名と市区町村名の組み合わせを含む無数の区分があります。
世界中の国別も含めたドメインの区分は数え切れないと思います。
また、コンピューター名を含むURLと含まないURLも混在すると思いますので何処からがドメイン名なのか判断できないと思います。
従って、組み込み関数では対応できないでしょう。

回答No.5

bunjii

>A列のセルに一つずつURLが記入されています。
例示のURLでプロトコルがhttp://以外にhttps://やftp://は存在しないと考えて良いでしょうか？
また、ドメインの区分で.com以外に.orgや.net、.co.jp、.jp等は無いのでしょうか？

>そして、「〇〇」のドメイン名だけをC列に表示。
「http://〇〇.〇〇.com/〇〇/」の場合は「〇〇.〇〇」をC列へ抽出すれば良いのでしょうか？

数式を組むためには上記の疑問が解けないと正しい結果を得られないことも起こります。
取敢えずB列へ抽出する文字列は下記の数式で良いと思います。
=SUBSTITUTE(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",3)&"|")-1),LEFT(A1,FIND("://",A1)+2),"")

補足

回答ありがとうございます。

> 例示のURLでプロトコルがhttp://以外にhttps://やftp://は存在しないと考えて良いでしょうか？

はい、http://のみです。

> また、ドメインの区分で.com以外に.orgや.net、.co.jp、.jp等は無いのでしょうか？

.com以外に、存在するドメイン全てが対象になります。

> 「http://〇〇.〇〇.com/〇〇/」の場合は「〇〇.〇〇」をC列へ抽出すれば良いのでしょうか？

サブドメインを含めない形を抽出したいです。
なので、「〇〇.com」の「〇〇」です。

=SUBSTITUTE(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",3)&"|")-1),LEFT(A1,FIND("://",A1)+2),"")
でできました！
ありがとうございます。

回答No.4

msMike

B1: =MID(A1,FIND("//",A1)+2,FIND(".com/",A1)-FIND("//",A1)+2)

回答No.3

kteds

URL文字列を切り取ればいいです。
B列は添付画像の「抽出 1」でいいでしょう。(画像の数式は (B,4) の数式です)
C列、D列も同じ要領で抽出すればいいでしょう。
自分で応用してみてください。

回答No.2

山田 太郎（@f_a_007）

次のCutStr関数を利用すれば、容易に目的を達成できるかも知れません。

Public Function CutStr(ByVal Text As String, _
　　　　　　　　　　　　ByVal Separator As String, _
　　　　　　　　　　　　ByVal N As Integer) As String
　　Dim strDatas() As String

　　strDatas = Split("" & Separator & Text, Separator, , 0)
　　CutStr = strDatas(N * Abs(N <= UBound(strDatas)))
End Function

CutStr("テキスト", 区切り子 Or 区切り文, 抜き出す番目)

利用方法は、添付図を参考にされて下さい。

回答No.1

ketsuro8da

エクセルのオートフィルターという機能を使うと可能かも知れません。お調べになって見てください。