excelで"末尾に特定の文字列を含まない"行を削除したい

質問

excelでセル末尾に特定の文字列を含まない行を削除したいのですが、どのようにすればいいのでしょうか。

実際の書類はA列のみにずらっとメールアドレスが３万行並んでおり、この中には不完全なメールアドレス"hoge@hoge.jp.ne"等があるため、一般的な末尾に来るべき文字列".jp" ".com" ".net" などが末尾にないセルを削除したいです。また、"hoge@hoge@hoge.jp" というアドレスも存在するため、"@"が２個以上含まれたセルも削除できると助かります。

マクロの実行方法はわかりますが勉強不足で書き方がわかりません。我が儘ですが、お知恵を拝借いただけると幸いです。よろしくお願い致します。

回答No.2

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

B1セルに
=OR(RIGHT(A1,LEN($D$1:$D$3))=$D$1:$D$3)*(LEN(SUBSTITUTE(A1,"@",""))=LEN(A1)-1)
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)
タイトルをつけて1のみ抽出する。添付図参照

お礼

ありがとうございます、できました！　恥ずかしながら配列数式というものを初めて知りました。マクロなど使わなくてもできるのですね。怪しいアドレスがうようよ出てきて非常に助かりました。ありがとうございました！

回答No.1

mshr1962

■一般的な末尾に来るべき文字列".jp" ".com" ".net" などが末尾にないセル
=SUMPRODUCT(RIGHT(A2,MAX(LEN($X$1:$X$10),1)=$X$1:$X$10)*1)
X1:X10に".jp" ".com" ".net" などを記入して上記の結果が0の行を削除する。

■"@"が２個以上含まれたセル
=(LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))>1
上記の結果がTRUEの行を削除する

お礼

ご指導ありがとうございます。早速試してみましたがなぜかエラーでできませんでした。「数式を計算できません。開いているブックに循環参照がありますが、この場合、循環参照は表示されません。」と出てしまいました。この数式をB列のセルに入れてみたのがいけなかったのでしょうか。"@"の方もなぜか全てFALSEになってしまいました。mac版excelなのが悪いのかもしれません。質問にその旨記載するべきでした。申し訳ございません。ご回答ありがとうございました！