【エクセル】特定文字間の抽出

質問

アクセス解析のURL抽出についてお教えください。

エクセル：2013

行　｜　抽出文字列（C列）
１１｜en/stay/index.php
１２｜en/index.php
１３｜en/experience/index.php
１４｜en/healing/index.php
１５｜en/sports/index.php
１６｜en/index.html
※各言語で合計で２００ページ程度あります。
◎ルールとしては
言語/カテゴリ/ページ名
index.html（１６行）以外ならば下記式で抽出できるのですが、index.htmlのの場合”/”が一回しか出現しないためエラーとなります。
"=MID(C10,FIND("/",C10)+1,find("/"C10,4)-find("/",C10)

”言語／ページ名”の場合と”言語／カテゴリ／ページ名”の場合の抽出方法をお教え頂けましたら幸いです。

回答No.1

dogs_cats · ベストアンサー

カテゴリーが無い場合はその列は空白を返すので良いのですか？

C11：データ
D11:ページ名
E11：カテゴリ
F11：ページ名

D11式
=LEFT(C11,FIND("/",C11,1))
E11式
=IF(LEN(C11)-LEN(SUBSTITUTE(C11,"/",""))>1,LEFT(SUBSTITUTE(C11,D11,""),FIND("/",SUBSTITUTE(C11,D11,""),1)),"")
F11式
=SUBSTITUTE(SUBSTITUTE(C11,D11,""),E11,"")

D11~F11を下方へオートフィル

E11式は、質問の数式では最初のスラッシュが４文字目以降に無いと数式が成り立たちません。言語の文字を空白置換した後にfind関数でスラッシュを検索すれば、言語の文字数に依存されない方法として修正しました。

お礼

dogs_cats様
回答有難うございます。
完璧です。

回答No.2

bunjii

>”言語／ページ名”の場合と”言語／カテゴリ／ページ名”の場合の抽出方法をお教え頂けましたら幸いです。
IF関数で条件分岐すれば良いでしょう。
条件式は次のようにすれば判定できます。
ISERROR(FIND("/",SUBSTITUTE(C2,"/","",1)))
FIND関数で"/"の位置を検索するとき検索対象の文字列をSUBSTITUTE関数で最初の"/"を削除または他の文字に置き換えると”言語/ページ名”のときエラーになります。
そのエラーの有無で条件分岐すれば目的に合います。
カテゴリの切り出し
=IF(ISERROR(FIND("/",SUBSTITUTE(C2,"/","",1))),"",MID(C2,FIND("/",C2)+1,FIND("/",SUBSTITUTE(C2,"/","X",1))-FIND("/",C2)-1))
ページ名の切り出し
=IF(ISERROR(FIND("/",SUBSTITUTE(C2,"/","",1))),MID(C2,FIND("/",C2)+1,LEN(C2)),MID(C2,FIND("/",SUBSTITUTE(C2,"/","X",1))+1,LEN(C2)))

お礼

bunjii
回答有難うございます。
なるほど、エラーで分岐すればよいのですね、大変勉強になりました。