Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Home Premium : Excel 2010)
InStr関数について
投稿日時: 20/03/07 00:20:30
投稿者: shimoichimabu

A A - B B- CCC の文字で、
 
検索文字列 "- " とする場合は文字列の10番目という値を取得したいです。
検索文字列 " -" とする場合は文字列の0番目という値を取得したいです。(検索文字列なし)
検索文字列 " - "とする場合は文字列の4番目という値を取得したいです。
 
InStr("AA - BB- CCC","- ") だと、当然ながら、4という値が返ってきます。
 
まともな方法では、無理そうで、何か工夫しないとできそうもありません。
名案はないでしようか?

回答
投稿日時: 20/03/07 09:30:05
投稿者: もこな2

質問がよく理解できませんが、こういうことでしょうか?

Sub 実験()
    Const 検索対象 As String = "A A - B B- CCC"
    Dim 検索値 As String
    Dim 結果 As String
    Dim tmp As Long
    
    検索値 = InputBox("検索したい文字?")
    
    Stop
    Select Case 検索値
        Case Is = "- "
            結果 = Mid(検索対象, 10, 1)
        
        Case Is = " -"
            結果 = "検索文字列なし"
            
        Case Is = " - "
            結果 = Mid(検索対象, 4, 1)
            
        Case Else
            tmp = InStr(検索対象, 検索値)
            If tmp > 0 Then
                結果 = Mid(検索対象, tmp, Len(検索値))
            Else
                結果 = "みつかりませんでした"
            End If
    End Select
    
    MsgBox 結果

End Sub

回答
投稿日時: 20/03/07 10:01:39
投稿者: simple

こういうことですか?

Sub test1()
    Dim s As String
    Dim startpos As Long
    Dim m As Long
    s = "A A - B B- CCC"
     ' " - "ではなく、はじめて "- "にマッチした箇所
    startpos = 1
    Do
        m = InStr(startpos, s, "- ")
        If m = 0 Then
            Exit Do
        ElseIf Mid(s, m - 1, 1) <> " " Then
            Exit Do
        Else
            startpos = m + 1
        End If
    Loop
    Debug.Print m
End Sub

Sub test2()
    Dim s$
    Dim startpos&
    Dim m&
    s = "A A - B B- CCC"
    ' " - "ではなく、はじめて " -"にマッチした箇所
    startpos = 1
    Do
        m = InStr(startpos, s, " -")
        If m = 0 Then
            Exit Do
        ElseIf Mid(s, m + 2, 1) <> " " Then
            Exit Do
        Else
            startpos = m + 2
        End If
    Loop
    Debug.Print m
End Sub

回答
投稿日時: 20/03/07 10:05:24
投稿者: simple

正規表現を使えば、こんなことになります。ご参考まで。

Sub test()
    Dim re      As Object
    Dim matches As Object
    Dim s       As String
    
    s = "A A - B B- CCC"
    Set re = CreateObject("VBScript.RegExp")
    With re
        .Pattern = "[^ ]\- "      '(1) 空白以外の文字に続く "- "
        '.Pattern = " \-[^ ]"     '(2) " -"に空白以外の文字が続く
        '.Pattern = " \- "        '(3) " -"に空白以外の文字が続く

        Set matches = .Execute(s)
        If matches.count > 0 Then
            Debug.Print matches(0).FirstIndex + 1 + 1   '(1)のとき
            'Debug.Print matches(0).FirstIndex + 1      '(2)(3)のとき(indexが0オリジン)
        Else
            Debug.Print 0
        End If
    End With
End Sub

(3)のケースはむろん、InStrを普通に使えばよいだけですが。

回答
投稿日時: 20/03/07 10:07:01
投稿者: mattuwan44

引用:
検索文字列 "- " とする場合は文字列の10番目という値を取得したいです。
検索文字列 " -" とする場合は文字列の0番目という値を取得したいです。(検索文字列なし)
検索文字列 " - "とする場合は文字列の4番目という値を取得したいです。

 
検索文字列はその3種類限定でしょうか?

投稿日時: 20/03/07 10:16:33
投稿者: shimoichimabu

もこな2 さん回答ありがとうございます。
 
本当に説明が悪く、申し訳ございません。
 
Sub 実験()
    Const 検索対象 As String = "A A - B B- CCC"
    Dim 検索値 As String
    Dim 結果 As String
    Dim tmp As Long
 
検索値 = InputBox("検索したい文字?")
     
    Stop
    Select Case 検索値
        Case Is = "- "
            結果 = Mid(検索対象, 10, 1)
・・・・・・・・・・・・・・・・・・・・・ のコードにおいて、
 
結果 = Mid(検索対象, 10, 1) の場合で申しますと、
 
結果=????????? の?部分でどんな関数式、もしくは処理になるかもしれませんが、
 
結果=10を取得したいです。
 
もし、"- "が存在しなければ、結果=0 を取得したいです。
 
" - "
"- "
" -"
 
をそれぞれ、独立した異なる文字列として、Excel側に認識できれば、いいのですが・・・・。
 
 
 

回答
投稿日時: 20/03/07 10:18:28
投稿者: simple

    Dim s$
    Dim startpos&
という宣言方法を修正し漏れました。
むろん間違いではないですが、
    Dim s As String
    Dim startpos As Long
と同じ意味です。
 
型宣言文字というもので、ヘルプにも書かれています(String,Long等の項)。
入力が少なくて済むので、個人的には使用していますが、
慣れないと戸惑うので、普通はこれを使って回答することは控えています。
今回は修正を漏らしてしまいました。読み替えてください。

回答
投稿日時: 20/03/07 10:30:11
投稿者: simple

訂正です。
正規表現のコードですが、"-" は\でエスケープする必要はなかったです。
 
[0-9]のように文字列の範囲を示す"-" は、特殊文字ということでもなかったので、
以下の書き方で問題なかったですね。(動作に影響はありませんが。)
        .Pattern = "[^ ]- " '(1) 空白以外の文字に続く "- "
        .Pattern = " -[^ ]" '(2) " -"に空白以外の文字が続く
        .Pattern = " - " '(3) " -"に空白以外の文字が続く

投稿日時: 20/03/07 10:33:33
投稿者: shimoichimabu

もこな2さんの回答の返事を作成している間に、
simpleさん、mattuwan44さんから回答いただき、ありがとうございます。
 
>検索文字列はその3種類限定でしょうか?
3種類限定です。
 
現在、simpleさんの回答を見させていただいております。
正規表現など、理解するのにちょっと、時間がかかりそうです。

回答
投稿日時: 20/03/07 10:34:53
投稿者: simple

正規表現は横に置いて下さって結構です。

回答
投稿日時: 20/03/07 11:00:45
投稿者: simple

たびたびの修正で恐縮ですが、エッジケースの考慮が足りませんでした。
下記に修正してください。
 

Sub test1()
    Dim s As String
    Dim startpos As Long
    Dim m As Long
    
    's = "A A - B B- CCC"
    s = "- B B - DD"
    
    ' " - "ではなく、はじめて "- "にマッチした箇所
    startpos = 1
    Do
        m = InStr(startpos, s, "- ")
        If m = 0 Or m = 1 Then
            Exit Do
        ElseIf Mid(s, m - 1, 1) <> " " Then
            Exit Do
        Else
            startpos = m + 1
        End If
    Loop
    Debug.Print m
End Sub

test2にも同様の趣旨から、" -"で終わる場合の考慮が必要ですが、
必要ならそちらで修正してください。

回答
投稿日時: 20/03/07 11:03:30
投稿者: WinArrow
投稿者のウェブサイトに移動

>Const 検索対象 As String = "A A - B B- CCC"
 
検索文字列:"- "
10を取得したい
 
ということですが、
"- "のパターンは、2ヶ所存在します。
どのように判断すればよいでしょうか?

回答
投稿日時: 20/03/07 11:27:48
投稿者: simple

そうですね。問題を明示的に説明していないですよね。
こちらも勝手解釈しているわけで、一度、きちんと説明する必要があるでしょうね。

回答
投稿日時: 20/03/07 12:40:12
投稿者: mattuwan44

Option Explicit

Function orgInStr(ByVal sTarget As String, ByVal sKeyWord As String) As Variant
    Dim i As Long
    i = 1
    Select Case sKeyWord
        Case " - "
            i = InStr(i, sTarget, sKeyWord)
        Case " -", "- "
            Do
                i = InStr(i, sTarget, sKeyWord)
                If i = 0 Then Exit Do
                If InStr(Mid(sTarget, i - 1, 4), " - ") = 0 Then Exit Do
                i = i + 1
            Loop
    End Select
    orgInStr = i
End Function

Sub test()
    MsgBox orgInStr("A A - B B- CCC", " -")
    MsgBox orgInStr("A A - B B- CCC", "- ")
    MsgBox orgInStr("A A - B B- CCC", " - ")
End Sub

 
" -","- "の時は、
見つかった位置で前後に広げて" - "ではないかをチェックしたらいいんじゃないですかね。。。。
 
スペース文字以外の文字というような指定が、出来れば、考え方が楽になるんでしょうけど、、、、
正規表現ならそれが出来るかもしれませんが、にわかにはパターンの書き方が理解出来ないですよね><
本買ったけど、1行も読まずに本棚の奥に。。。。。^^;

回答
投稿日時: 20/03/07 12:44:18
投稿者: WinArrow
投稿者のウェブサイトに移動

再度の確認
 

引用:

検索対象文字列:"A A - B B- CCC"
 
(1)検索文字列 "- " とする場合は文字列の10番目という値を取得したいです。
(2)検索文字列 " -" とする場合は文字列の0番目という値を取得したいです。(検索文字列なし)
(3)検索文字列 " - "とする場合は文字列の4番目という値を取得したいです。

 
現状では
(1)の結果は、5・・・・お望みは、10
  別の何らかの情報が必要ですね・
(2)の結果は、4・・・・お望みは、0
  別の何らかの情報が必要ですね・
(3)の結果は、4・・・・お望みは、4
   これはお望み通りです。

回答
投稿日時: 20/03/07 13:04:13
投稿者: WinArrow
投稿者のウェブサイトに移動

パターン分けを連続技で
 
Sub test()
Dim 文字列 As String, St As Long, SSS As String
Dim k As Long
 
    文字列 = "A A - B B- CCC"
    St = 1
    SSS = " - "
    k = InStr(St, 文字列, SSS)
    Debug.Print "(1):[" & SSS & "]="; k
    SSS = "- "
    St = k + Len(SSS)
    k = InStr(St, 文字列, SSS)
    Debug.Print "(2):[" & SSS & "]="; k
    SSS = " -"
    St = k + Len(SSS)
    k = InStr(St, 文字列, SSS)
    Debug.Print "(3):[" & SSS & "]="; k
 
End Sub

回答
投稿日時: 20/03/07 17:00:26
投稿者: VBA-stepup

お邪魔します。
 

引用:

A A - B B- CCC の文字で、
  
検索文字列 "- " とする場合は文字列の10番目という値を取得したいです。
検索文字列 " -" とする場合は文字列の0番目という値を取得したいです。(検索文字列なし)
検索文字列 " - "とする場合は文字列の4番目という値を取得したいです。
 

検索文字列 "- " ハイフンの前の文字は半角スペースでは無いハイフンの位置
検索文字列 " -" ハイフンの後の文字は半角スペースでは無いハイフンの位置
検索文字列 " - " ハイフンの前後の文字が半角スペースのハイフンの位置
 
上記の条件とハイフンが2ヶ所の限定で
 
Sub test1()
    Dim v As Variant
    Dim k As Long
    
    v = "A A - B B- CCC"
    
    If Mid(v, InStr(v, "-") - 1, 1) <> " " Then
        k = InStr(v, "-")
    Else
        If Mid(v, InStrRev(v, "-") - 1, 1) <> " " Then
            k = InStrRev(v, "-")
        End If
    End If
    MsgBox "[- ]  検索は" & k
    k = 0
    If Mid(v, InStr(v, "-") + 1, 1) <> " " Then
        k = InStr(v, "-")
    Else
        If Mid(v, InStrRev(v, "-") + 1, 1) <> " " Then
            k = InStrRev(v, "-")
        End If
    End If
    MsgBox "[ -]  検索は" & k
    
    k = 0
    k = InStr(v, " - ")
  MsgBox "[ - ]  検索は" & k
End Sub

投稿日時: 20/03/07 17:55:29
投稿者: shimoichimabu

simpleさん、WinArrowさん、mattuwan44さん回答ありがとうございました。
 
下記の方法を教えて頂き、どれも役立ちました。
自分のスキルが乏しく、理解するのが大変です。
 
(第1法)
正規表現できるのですね。詳しいことはまだ理解できていません。
 
(第2法) 以下のようにinStrの検索開始位置を変数に使う。
Do
   i = InStr(i, sTarget, sKeyWord)
   If i = 0 Then Exit Do
   If InStr(Mid(sTarget, i - 1, 4), " - ") = 0 Then Exit Do
   i = i + 1
Loop
 
(第3法) orgInStr()のように関数式を作ってしまう。
" -","- "の時は、
 見つかった位置で前後に広げて" - "ではないかをチェックする。
 
第4法 Lenを使う方法
・・・・・・・・・・・
 St = k + Len(SSS)
・・・・・・・・・・・
 
私の説明不足で皆様にご迷惑かけています。
 
検索文字列は、" - " "- " " -" の3種です。
検索文字列がはじめて " -"にマッチした箇所を求める。例.10番目
 
WinArrowさんから
>"- "のパターンは、2ヶ所存在します。どのように判断すればよいでしょうか
というご指摘がありましたように、どうも" - "が問題をむつかしくしていると思い、
これを潰してしまえばいいかと思い、下記コードを作って
 
Sub Macro1()
 
Dim s As String, 検索文字 As String, m As Long
     
    s = "A A - BB- CCC"
    検索文字 = " -"
     
    s = Replace(s, " - ", "▲-▲") ’ここで " - " を潰しておく
     
    If 検索文字 = " - " Then
        m = InStr(s, "▲-▲")
    Else
        m = InStr(s, 検索文字)
    End If
 
  msgbox 検索文字 & "は" & m & "番目です。"
     
End Sub
 
このコードで私の希望を汲み取っていただければと思い、提示いたしました。
ただ、このコードに穴があれば、ご指摘ください。

投稿日時: 20/03/07 18:16:28
投稿者: shimoichimabu

訂正です。
 
検索文字列がはじめて " -"にマッチした箇所を求める。例.10番目
         ↓
検索文字列がはじめてマッチした箇所を求める。例.10番目

投稿日時: 20/03/07 18:44:26
投稿者: shimoichimabu

VBA-stepupさん回答ありがとうございました。
>上記の条件とハイフンが2ヶ所の限定で
といことですが、参考にさせてもらいます。
 
たびたびの修正、申し訳ありません。
 
↓" - ","- "," -" が順不同で、重複してもOKでした。
s = "A A- B B- CCC - D D -EE"
 
Dim s As String, k As String, y As String, m As Long
    s = "A A- B B- CCC - D D -EE"
    検索文字 = " -"
     
    k = Replace(s, " - ", "▲-▲")
     
    If 検索文字 = " - " Then
        y = InStr(k, "▲-▲")
    Else
        y = InStr(k, 検索文字)
    End If
 
  msgbox 検索文字 & "は" & y & "番目です。"

回答
投稿日時: 20/03/07 20:06:22
投稿者: VBA-stepup

出来たようですね。
s = Replace(s, " - ", "▲-▲") ’ここで " - " を潰しておく
置換ですか。
 
VBAにはこのような発想の転換が重要ですね
大変参考になりました。有難うございます。

回答
投稿日時: 20/03/07 20:13:10
投稿者: takesi

Len文字数。。ちょっと作ってみました。

Sub tes1()
    Dim s As String
    Dim lngCnt As Long
    Dim varRet  As Variant
    Dim ForFlg  As Integer
    Dim BacFlg  As Integer
    
    s = "A A - B B- CCC-dd -eee"
    
    ForFlg = 0
    BacFlg = 0
    lngCnt = 0
    varRet = 0
    varRet = InStr(varRet + 1, s, "-")
    Do
        If Len(Replace(Mid(s, varRet - 1, 2), " ", "")) = 2 Then ForFlg = 1
        If Len(Replace(Mid(s, varRet, 2), " ", "")) = 2 Then BacFlg = 2
        
        Select Case ForFlg + BacFlg
        Case 0
            Debug.Print "[ _ ] :" & varRet - 1
        Case 1
            Debug.Print "[_ ] :" & varRet
        Case 2
            Debug.Print "[ _] :" & varRet - 1
        Case 3
            Debug.Print "[*_*] :" & varRet
        
        End Select
        varRet = InStr(varRet + 1, s, "-")
        ForFlg = 0
        BacFlg = 0
    Loop Until varRet = 0
    
End Sub

投稿日時: 20/03/08 20:27:28
投稿者: shimoichimabu

 takesi さん回答ありがとうございます。
 
LEN関数、InStrの組み合わせで「-」の位置を取得する方法ですね。
スマートな方法ですね。
 
今回は色々と貴重な情報をいただき、感謝しております。
今後のプログラム作成に役立てたいです。