excelにて記号を取り出したい

質問

お世話になります。

セルの中に、例えば「アアア＠イイイ」といったような記号が含まれている文字列があります。その中の記号を検出したいのですが、うまくいきません。

最終的にやりたいことは、そのセルの中に記号が含まれている場合、エラー表示を出したいのですが、VLOOKUPなどを使ってみても、数式的にエラーになるばかりで、うまくいかないのです。

※記号単体でVLOOKUPを使うと確かに抽出されるのですが、文字列に紛れ込むと、検索しないみたいです。

なんとかならないものでしょうか。（ちなみに記号は不特定です）

回答No.9

imogasi · ベストアンサー

ユーザー関数（VBA）を定義すれば、正規表現度々使わなくても簡単なコードです。
Function kigou(a)
x = "@#$%&()="
For i = 1 To Len(x)
p = InStr(a, Mid(x, i, 1))
If p > 0 Then
kigou = Mid(x, i, 1) & "がある"
Exit Function
End If
Next i
kigou = "記号がない"
End Function
ーーー
（１）どの文字が記号かは
x = "@#$%&()="
のところで書き連ねて列挙する。
（２）最初に見つかった記号を返して終わりとしている。
記号を全て列挙もコードを少し変えて可能（注）
例データ
A列　　　B列
aah@wert @がある
a%%ggh %がある
dffghhh$jku $がある
asdfg 記号がない
B1は
=kigou(A1)
以下式を複写
(注）
Function kigou2(a)
x = "@#$%&()="
s = ""
For i = 1 To Len(x)
p = InStr(a, Mid(x, i, 1))
If p > 0 Then
s = s & Mid(x, i, 1) & ","
End If
Next i
If s = "" Then
kigou2 = "記号がない"
Else
kigou2 = Left(s, Len(s) - 1) & "あり"
End If
End Function
ーー
例データA5B7
as#3fg4 #あり
asdfg 記号がない
sddd$gg&6yy $,&あり
B5に=kigou2(A5)

回答No.8

noname#204879

arimasouitirouさん、再度の割り込みをお許しください。m(__)m

[ANo.6 maron--5]さんへのコメント、

懇切丁寧で、かつ、非常に分かり易い解説を多謝です。お蔭様でよく理解できました。と同時に maron--5 さんが Excel の性質に熟知された達人であることを再認識いたしました。

私にとって“目から鱗”的なポイントは次の事柄でした。

１．=FIND($D$1:$D$4,A1) は =FIND({"@";"#";"$";"*"},A1) と等価であること
２．式中の該当部分を指定して[再計算実行キー](F9)を叩くと計算結果が、上から順に次のように配列表示されること
　　={7;#VALUE!;#VALUE!;#VALUE!}
　　={#VALUE!;#VALUE!;8;#VALUE!}
　　={#VALUE!;7;#VALUE!;#VALUE!}
　　={#VALUE!;#VALUE!;#VALUE!;#VALUE!}
　　={#VALUE!;#VALUE!;#VALUE!;5}
３．=COUNT(FIND($D$1:$D$4,A1)) は数値の個数を返すので、そのまま上から順に 1、1、1、0、1 になるかと思ったら、そうではないこと。でも Shift＋Ctrl＋Enter を実行して {=COUNT(FIND($D$1:$D$4,A1))} とすればＯＫなこと。
４．しかし、=COUNT(FIND({"@";"#";"$";"*"},A1)) の場合は、Shift＋Ctrl＋Enter を実行してもしなくても、上から順に 1、1、1、0、1 になるようです。

実は、上のステップ３と４の違いがイマイチ理解できていません。

５．ステップ３の冒頭に示す式の場合は「INDEX関数を加えることで、先ほどの配列数式を同じ結果」になること
６．ステップ４の性質から、最終式はINDEX関数なしの次式でもＯＫのようですね。
　　=IF(COUNT(FIND({"@";"#";"$";"*"},A1)),"エラー","")

ありがとうございました。

回答No.7

maron--5

◆間違いがありましたので、訂正します（A1をB1に）
★COUNT(INDEX(FIND($D$1:$D$4,A1),))
１）この式をB1に入力して、下にコピーしてください。
２）数式バーの式全体を指定して、「F9」を押してくださいB2が「1」になりましたでしょうか。

★その他、読みにくい個所がありますがおゆるしください

回答No.6

maron--5

mike g さんへ（お役に立てれば幸いです）
◆maronは式を理解したり、回答する場合の確認にこの方法（「F9」）を使っていますので、それを使いながら説明させていただきます

★FIND($D$1:$D$4,A1)
１）B1を指定して、数式バーの「=IF(COUNT(INDEX(FIND($D$1:$D$4,A1),)),"エラー","")」の内
FIND($D$1:$D$4,A1) の部分のみ指定して、「F9」を押してください
２）{7;#VALUE!;#VALUE!;#VALUE!} と表示されたはずです
３）これは、$D$1:$D$4 の範囲の一番目の（＄D$1）@が、A1の文字列の7番目にあることを示しています
４）また、その他が#VALUE!ということは、その他の記号はA1に含まれていないことになります

★=COUNT(FIND($D$1:$D$4,A1))
１）この式を一度B1に入力してみてください
２）COUNTとSUMは数式にERRORがあっても計算してくれる、便利な関数です
３）B1の帰り値は、「1」です。この式を下にコピーしてください
４）B2は「0」になります。
５）次に、B2で数式バーの、COUNT(FIND($D$1:$D$4,A2)) を指定して、「F9」を押してください。「1」になります
６）また、B2で数式バーを指定して、Shift＋Ctrl＋Enter を押してください。同じく「1」になります
７）ですから、式を=IF(COUNT(FIND($D$1:$D$4,A1)),"エラー","") にして、Shift＋Ctrl＋Enterを押して、下にコピーすると同じ結果になります（この配列数式の回答でもいいと思います）
※A1の式が「1」になったのは、たまたまA1に一番上にある@があったためです

★COUNT(INDEX(FIND($D$1:$D$4,A1),))
１）この式をA1に入力して、下にコピーしてください。
２）数式バーの式全体を指定して、「F9」を押してくださいA2が「1」になりましたでしょうか。
３）INDEX関数を加えることで、先ほどの配列数式を同じ結果になります。
３）配列数式の「Shift＋Ctrl＋Enter を押す」手順を回避できます
４）質問者の方がよく配列数式を理解されず、「Shift＋Ctrl＋Enter を押す」手順を省略や間違ったりされることがあるので
このINDEX関数を使った式を回答しました（maronの勝手ですが）
５）INDEX関数は、配列を顕在化させることができるようです

回答No.5

noname#204879

割り込みで失礼します。

[ANo.4]の maron--5さん、

いつも勉強させてもらっています。
今回の回答式を分解してみましたが、どうしても理解できず、眠れなくなって困っています。(^_^)
COUNT(INDEX(FIND($D$1:$D$4,A1),)) の部分を解説していただけないでしょうか？
お願いします。

回答No.4

maron--5

A　　　　　　　　　　B　　　　C　　　　D
1　　　qqwqed@wedwe　　　エラー　　　　　　@
2　　　qeedwqe$sadsad　　エラー　　　　　　　#
3　　　uwguwg#　　　　　　　エラー　　　　　　　$
4　　　fyu　　　　　　　　　　　　　　　　　　　　　*
5　　　dstd*uuydfc　　　　　エラー

B1=IF(COUNT(INDEX(FIND($D$1:$D$4,A1),)),"エラー","")
★下にコピー

回答No.3

mitarashi

正規表現を使った、ユーザー定義関数はいかがでしょうか
....................A.................B...............C
..1....aaa@aaaa.....[@\-\*].........TRUE
..2....aaa-aaaa.....[@\-\*].........TRUE
..3....aaa*aaaa.....[@\-\*].........TRUE
..4......aaaaaaa.....[@\-\*].......FALSE
B列に、チェック対象の文字群を、全体を[]で囲って記述します。[abcdefg]とすれば、a～gをチェックする事になります。
ここで、-や、*は演算子と判断されるため、頭に\をつけています。
関数はC列に入れてあります。
そのままVBAで使っても使えるはずです。（重いと思うので、大量に使うにはVBAで処理した方が良いでしょう）
詳細は参考URLなどをご覧下さい。

Function searchWord(targetString As String, patternString As String) As Boolean
Dim regEx As Object, Matches As Object, match As Object

Set regEx = CreateObject("VBScript.RegExp")
regEx.MultiLine = False
regEx.Pattern = patternString
regEx.IgnoreCase = False '大文字小文字を区別する
regEx.Global = False '一個見つかったら終了
Set Matches = regEx.Execute(targetString)
If Matches.Count > 0 Then
searchWord = True
Else
searchWord = False
End If
Set Matches = Nothing
Set regEx = Nothing
End Function

参考URL：

http://officetanaka.net/excel/vba/tips/tips38.htm

回答No.2

keirika

A1に文字列があると仮定します。

=IF(LEN(A1)=LEN(SUBSTITUTE(SUBSTITUTE(A1,"@",""),".","")),"OK","ERROR")

でどうでしょうか。

補足

ご回答ありがとうございます。
確かに、ERRORになります。う～～～んですが、これだと指定する記号を全部SUBTITUTEで括らないといけませんよね～～？
（範囲指定とかできれば、別表にでも記載するのですが；；）

記号が2つ3つならいいんですが、6個ぐらいありますので、この方法だとちょっと面倒だし、数限りあるという気がするのですが…わかがままでしょうか。

本当、申し訳ありません。

回答No.1

noname#77845

補足してください。

「ちなみに記号は不特定です」
と書かれていますが、
「a」、「A」、「@」、「$」
エクセルから見たら全て記号です。
何をもって記号と判断したら良いのでしょう？

補足

すみません、補足します。

いわゆる、「-」とか「*」とか「@」とかです。
でも、文字列に紛れているので（文字列は2バイト文字ではないです）
例：aaa@bbb.com　←の、「＠」と「.」を検出し、それらが含まれている場合、エラーを表示させたい。

…というようなことなんですが…。できますでしょうか。