【決定版】EXCELでURLからドメインのみを抽出する関数

ドメインを抽出したいよ

ホームページや適当なサイトのURLからドメインを抜き出したいときに役に立つ関数を作りました。
このコードだと、他のサイトのものに比べて

  • 余計なwww.が消せる（必要でも改修が簡単）
  • URLの末尾に/があってもエラーにならない
  • http://  https://の両方に対応
  • EXCELとgoogle docsで動作確認済み

といったメリットがあります。これが決定版と銘打った理由です。

「www.」を残したいときはSUBSTITUTE(～, “www.”, “”)を消してください。

仕組みはどうでもいいから早く使わせろという方は以下をコピペして、B2を任意のセル番地に置き換えてください。

ホームページや適当なサイトのURL


=SUBSTITUTE(SUBSTITUTE(LEFT(RIGHT(B2, LEN(B2) - FIND("://", B2) - 2), IFERROR(FIND("/", RIGHT(B2, LEN(B2) - FIND("://", B2) - 2)), LEN(RIGHT(B2, LEN(B2) - FIND("://", B2) - 2))) ), "www.",""), "/", "")

変換例

  • https://www.toritakashi.com -> toritakashi.com
  • https://toritakashi.com/test/index.html -> toritakashi.com

続いて、ホームページや適当なサイトのURLの抽出の仕組みを下に書きます。
複雑な処理も、しょせんは単純な処理の複合体にすぎません。

問題を分割し、適切に処理してやればよいのです。

ホームページや適当なサイトのURLからドメインを抽出する

コード自体は長いですが、関数ごとに分けて考えればそこまで難しいことをしているわけではありません。
かっこのネストが多いのでどんな順番で評価されているかがわかりにくいだけです。

試しにhttps://www.toritakashi.com/test/index.htmlをtoritakashi.comに変換してみましょう。

まず上の処理を言葉にすると

  1. 「://」より後（右=RIGHT）を抽出する
    -> www.toritakashi.com/test/index.html
  2. 1つ目の「/」以前（左=LEFT）を抽出する
    -> www.toritakashi.com/
    ただ、最初のURLがhttps://www.toritakashi.comだった場合「/」が無くてエラーになるので、その時は処理1と同じものを出力する
  3. もしURLに「www.」が含まれていたら削除する（空の文字列に置き換える）
    -> toritakashi.com/
  4. もしURLに「/」が含まれていたら削除する（空の文字列に置き換える）
    -> toritakashi.com

という構造になっています。

ネットで調べると、みんなMIDがお好きなようですね。
MIDは一度の式評価で処理しないといけないので、可読性が下がりそうです。

コードの説明

  1. 「://」より後（右=RIGHT）を抽出する
    RIGHT(B2, LEN(B2) - FIND("://", B2) - 2)
    「://」以降を抽出しています。
  2. 1つ目の「/」以前（左=LEFT）を抽出する
    LEFT(RIGHT(B2, LEN(B2) - FIND("://", B2) - 2), IFERROR(FIND("/", RIGHT(B2, LEN(B2) - FIND("://", B2) - 2)), LEN(RIGHT(B2, LEN(B2) - FIND("://", B2) - 2))) )
    長いですね、もっと分解しましょう。
    まずはIFERRORについてみてみます。

    IFERROR(FIND("/", RIGHT(B2, LEN(B2) - FIND("://", B2) - 2)), LEN(RIGHT(B2, LEN(B2) - FIND("://", B2) - 2)))
    このうち赤の太字になっている部分は1と全く同じ、つまり1で吐かれた文字列を指しています。
    この処理では1の出力について、最初に「/」がヒットすれば、その位置が何文字目かを。
    もしヒットしなければ＝エラーが出たならば、1の出力の一番最後が何文字目かを返します。

    そして最初の「/」以前をLEFT()によって抽出しています。

  3. もしURLに「www.」が含まれていたら削除する（空の文字列に置き換える）
  4. もしURLに「/」が含まれていたら削除する（空の文字列に置き換える）
    こちらは一番外側のSUBSTITUTE(～, “str”, “”)が該当しています。

おわりに

この記事を書いた後に一時変数を宣言できるLET()の存在を知りました。

Togetter
 
Excelに変数を定義できるLET関数が追加されるらしいぞ
2020/3/18 ExcelにLET関数という「数式内で変数を定義する関数」が追加されるらしいので使い方を調査するとともに、関連ツイートを収集しました。

一時変数がセル内で定義できるようになるといいですね。

もし不具合などがあればコメントの方によろしくお願いいたします。