Web関係の解析を行っていると、URLからドメイン部分のみ抽出したい時があります。
例
https://bluegoat.jp/blog/excel-url-domain/
↓
https://bluegoat.jp/
↓
https://bluegoat.jp/
この作業、エクセルでは
=LEFT(URLのセル,FIND("@",SUBSTITUTE(URLのセル,"/","@",3)))
と入力することで、ドメイン部分のURLのみ抽出することができます。
計算式の仕組み
この計算式は
- SUBSTITUTE
- FIND
- LEFT
の３つの関数が使われています。
SUBSTITUTE(文字列, 検索文字列, 置換文字列, 置換対象)
SUBSTITUTE関数は、文字列から検索文字列を検索し、置換文字列で置き換えることができます。
SUBSTITUTE(URLのセル,"/","@",3)の場合は、
URLの3番目の"/"を"@"に置き換えるという意味になります。
補足(最後の数字が無い場合)
=SUBSTITUTE(A1,"/","@")
↓
https:@@bluegoat.jp@blog@excel-url-domain@
すべての検索文字を置換文字で置き換えます。
FIND（検索文字列, 文字列）
FIND（検索文字列, 文字列）は、検索文字列から最初に一致した文字列までの文字数を返します。
=FIND("@","https://bluegoat.jp@blog/excel-url-domain/")
↓
20
（最初に"@"が出現するまでの文字数=20を返します）
↓
20
（最初に"@"が出現するまでの文字数=20を返します）
LEFT（文字列, 文字数）
LEFT関数は、文字列の左から数えた文字数を切り取った文字列を返します。
FIND関数でURLの中で3番目の"/"の位置が特定できているので、3番目の"/"の位置までを切り取ります。
＝LEFT（"https://bluegoat.jp/blog/excel-url-domain/", 20）
↓
https://bluegoat.jp/
左から数えた文字数を切り取った文字列を返します。
↓
https://bluegoat.jp/
左から数えた文字数を切り取った文字列を返します。
これらの３つの関数を組み合わせることで、ドメイン部分のみ抽出できます。
なお、"http://"や"https://"も削除したい場合は、置換機能を使っています。
