【ExcelVBA】文字列操作で業務を快適に②【Instr関数を使いこなす】

みなさん、こんにちはケンケンです。

今回は、文字列操作の２回目としてInstr関数を主に勉強していきます。

前回に使った資料を基に進めていきますので、まだご覧になっていない方はこちらを確認ください。

この関数を使えるようになると、文字列操作の幅が大きく広がるので頑張って覚えてしまいましょう。

Instr関数を使って「県」の位置を測定する

まずは、Instr関数について解説していきます。

この関数は、特定の文字列の中にある特定の文字を検索し、その文字の位置を返します。

言葉ではややこしいですね。

Instr(string1,string2)

構成は以上のようになっていて、string1に文字列を入れ、string2に検索したい文字を入れます。

具体的な使い方は、

Instr（”あいうえお”,”え”)

とすると、結果は４が返ってきます。文字列「あいうえお」の中に「え」は４文字目にあるからです。

この性質を生かして前回作った所在地一覧を作り直し、よりグレードの高い資料を作っていきます。

前回の資料は以下のとおりです。

D列に都道府県名を書き出し、それ以下をE列に書き出しました。

一見問題ないように見えますが、そうではありません。D列に書き出した時の条件が、C列の文字列の左から３文字分だったので、

都道府県名が和歌山県や神奈川県のように４文字の県では対応できません。

これを解消するためにInstr関数を使います。

それでは、第一段階としてC列の文字列の中から「県」を探してその値を変数に格納し、その値をイミディエイトウィンドウに表示してみます。

イミディエイトウィンドウは、変数の値を確認したりコードを実行する前に確認するときなどに使う機能です。

VBEの表示からイミディエイトウィンドウを選択すると、上記のようなウィンドウが表示されます。

これがイミディエイトウィンドウです。

ここに、確認事項を直接打ち込むか、特殊なコードを実行することで表示させます。

今回は、特殊なコードであるDebug.printを使ってウィンドウの中に確認したい変数の値を表示させてみます。

以下のコードを書いて実行してください。

Sub hogehoge()
    Dim hensuu
    hensuu = 4
    Debug.Print hensuu
End Sub

ウィンドウ内に「4」と表示されていればOKです。

変数の変動が大きいときにチェックする場合などにも便利なので使ってみてください。

話が反れてしまいましたがInstr関数に戻ります。

以下のコードを書いて実行します。

Sub moji_instr()
    Dim todoufuKen
    
    todoufuKen = InStr(Range("C2").Value, "県")
    Debug.Print todoufuKen
End Sub

イミディエイトウィンドウには「３」と表示されるはずです。

コードを解説すると、まず、変数todoufuKenを宣言します。

そしてInstr関数を使ってC2の文字列の中に「県」が何文字目にあるか確認し、その位置を変数に格納します。

最後に格納した数字Debug.printを使ってイミディエイトウィンドウに表示します。

ちなみに、Instr関数で検索した文字が発見できなかった時は「０」を返します。

C2の文字列は「香川県丸亀市福島町9-3-8」なので「県」は３文字目でOkですね。

ここまでできたら前回の方法で文字列を分けます。

具体的なコードは以下の通りです。

Sub moji_instr()
    Dim todoufuKen
        
    todoufuKen = InStr(Range("C2").Value, "県")
    Range("D2").Value = Left(Range("C2").Value, todoufuKen) '左から県があった箇所まで書き出す
    Range("E2").Value = mid(Range("C2").Value, todoufuKen + 1) '県の次から書き出す
End Sub

まず、Left関数で左から「県」があった箇所、つまり変数todoufuKenの値３までを書き出し、Mid関数で県の次の文字、つまりtodoufuKen+1（４文字目）から書き出します。

すると、県と県以下が分かれて記述されますね。

そして、それをループさせれば完成です。

Sub moji_instr()

    Dim todoufuKen
    
    Dim kenCnt
    
    For kenCnt = 2 To 17
        todoufuKen = InStr(Range("C" & kenCnt).Value, "県")
        Range("D" & kenCnt).Value = Left(Range("C" & kenCnt).Value, todoufuKen)
        Range("E" & kenCnt).Value = mid(Range("C" & kenCnt).Value, todoufuKen + 1)
    Next

End Sub

所在地一覧表の行をループさせる変数kenCntを用意しました。

これを実行すると以下のようになります。

１７行目に和歌山県を追加して実行してみましたが、ちゃんと県と県以下に分かれています。

しかし、条件が県の場合にのみに対応するため東京都では当然うまくいきません。

Debug.printを使うと東京都の時の変数が「０」になっていることがわかると思います。試してみてください。

つぎは、県だけでなく、都道府県すべてに対応させてみます。

「都道府県」の位置を測定する

では、都道府県すべてに対応させるにはどうすればよいでしょうか。

そうです。If構文を使って条件分岐させればできます。

まず、文字列の中に県が入っていたらそのまま実行し、県がなかったら文字列全部をD列に書き出してみます。

Sub todoufuken_kugiri()
    Dim todoufuKen
    
    If InStr(Range("C2").Value, "県") > 0 Then
        todoufuKen = InStr(Range("C2").Value, "県")
        Range("D2").Value = Left(Range("C2").Value, todoufuKen)
        Range("E2").Value = mid(Range("C2").Value, todoufuKen + 1)
    Else
        Range("D2").Value = Range("C2").Value
    End If
End Sub

ここで重要なのがIf以下の「InStr(Range(“C2”).Value, “県”) > 0」です。

さきほど少し触れましたが、Instr関数は検索した文字がなかったら「０」を返すので、逆を言えば０より大きい数だったら検索した文字は存在する、ということですね。

つまり、この条件文は、C2セルに県という文字があったら～をする、と言い換えることができます。その結果次第で実行結果を分けます。

実行して、県と県以下に分かれることを確認したら、次のようにコードを付け足します。

Sub todoufuken_kugiri()
    Dim todoufuKen
    
    If InStr(Range("C2").Value, "県") > 0 Then
        todoufuKen = InStr(Range("C2").Value, "県")
        Range("D2").Value = Left(Range("C2").Value, todoufuKen)
        Range("E2").Value = mid(Range("C2").Value, todoufuKen + 1)
    ElseIf InStr(Range("C2").Value, "都") > 0 Then
        todoufuKen = InStr(Range("C2").Value, "都")
        Range("D2").Value = Left(Range("C2").Value, todoufuKen)
        Range("E2").Value = mid(Range("C2").Value, todoufuKen + 1)
    ElseIf InStr(Range("C2").Value, "道") > 0 Then
        todoufuKen = InStr(Range("C2").Value, "道")
        Range("D2").Value = Left(Range("C2").Value, todoufuKen)
        Range("E2").Value = mid(Range("C2").Value, todoufuKen + 1)
    ElseIf InStr(Range("C2").Value, "府") > 0 Then
        todoufuKen = InStr(Range("C2").Value, "府")
        Range("D2").Value = Left(Range("C2").Value, todoufuKen)
        Range("E2").Value = mid(Range("C2").Value, todoufuKen + 1)
    Else
        Range("D2").Value = Range("C2").Value
    End If
End Sub

条件が複数になる場合は、ElseIfでつなげていきます。

一見複雑に見えますが、都道府県のいずれかが発見されたときにそれぞれ実行しているだけなので、ゆっくり理解してください。

最後にこれをループさせます。

Sub todoufuken_kugiri()
    Dim todoufuKen
    
    Dim todoufukenCnt
    
    For todoufukenCnt = 2 To 17
        If InStr(Range("C" & todoufukenCnt).Value, "県") > 0 Then
            todoufuKen = InStr(Range("C" & todoufukenCnt).Value, "県")
            Range("D" & todoufukenCnt).Value = Left(Range("C" & todoufukenCnt).Value, todoufuKen)
            Range("E" & todoufukenCnt).Value = mid(Range("C" & todoufukenCnt).Value, todoufuKen + 1)
        ElseIf InStr(Range("C" & todoufukenCnt).Value, "都") > 0 Then
            todoufuKen = InStr(Range("C" & todoufukenCnt).Value, "都")
            Range("D" & todoufukenCnt).Value = Left(Range("C" & todoufukenCnt).Value, todoufuKen)
            Range("E" & todoufukenCnt).Value = mid(Range("C" & todoufukenCnt).Value, todoufuKen + 1)
        ElseIf InStr(Range("C" & todoufukenCnt).Value, "道") > 0 Then
            todoufuKen = InStr(Range("C" & todoufukenCnt).Value, "道")
            Range("D" & todoufukenCnt).Value = Left(Range("C" & todoufukenCnt).Value, todoufuKen)
            Range("E" & todoufukenCnt).Value = mid(Range("C" & todoufukenCnt).Value, todoufuKen + 1)
        ElseIf InStr(Range("C" & todoufukenCnt).Value, "府") > 0 Then
            todoufuKen = InStr(Range("C" & todoufukenCnt).Value, "府")
            Range("D" & todoufukenCnt).Value = Left(Range("C" & todoufukenCnt).Value, todoufuKen)
            Range("E" & todoufukenCnt).Value = mid(Range("C" & todoufukenCnt).Value, todoufuKen + 1)
        Else
             Range("D" & todoufukenCnt).Value = Range("C" & todoufukenCnt).Value
        End If
    Next

End Sub

ループ用変数todoufukenCntを使ってC2など特定のセルを記述していた箇所を置き換えればOKです。

これを実行すると、

都道府県をきれいに分けることができました。

「市区町村」の位置を測定する

では、次は市区町村以下を分けてみましょう。

ここまで出来たらあとは簡単です。

都道府県を分けたように市区町村以下も分ければよいのです。

いろいろやり方はあるかと思いますが、今回は都道府県を分けた時に書き出したE列を使って分けてみます。

E列には都道府県以下が記述されているはずですね。

新しいプロシージャをつくって以下のようにコードを書いてみましょう。

Sub sikutyouson_kugiri() '市区町村を区切る

    Dim sikutyouson
    
    Dim sikutyousonCnt
    
    For sikutyousonCnt = 2 To 17
        If InStr(Range("E" & sikutyousonCnt).Value, "市") > 0 Then
            sikutyouson = InStr(Range("E" & sikutyousonCnt).Value, "市")
            Range("F" & sikutyousonCnt).Value = Left(Range("E" & sikutyousonCnt).Value, sikutyouson)
            Range("G" & sikutyousonCnt).Value = mid(Range("E" & sikutyousonCnt).Value, sikutyouson + 1)
        ElseIf InStr(Range("E" & sikutyousonCnt).Value, "区") > 0 Then
            sikutyouson = InStr(Range("E" & sikutyousonCnt).Value, "区")
            Range("F" & sikutyousonCnt).Value = Left(Range("E" & sikutyousonCnt).Value, sikutyouson)
            Range("G" & sikutyousonCnt).Value = mid(Range("E" & sikutyousonCnt).Value, sikutyouson + 1)
        ElseIf InStr(Range("E" & sikutyousonCnt).Value, "町") > 0 Then
            sikutyouson = InStr(Range("E" & sikutyousonCnt).Value, "町")
            Range("F" & sikutyousonCnt).Value = Left(Range("E" & sikutyousonCnt).Value, sikutyouson)
            Range("G" & sikutyousonCnt).Value = mid(Range("E" & sikutyousonCnt).Value, sikutyouson + 1)
        ElseIf InStr(Range("E" & sikutyousonCnt).Value, "村") > 0 Then
            sikutyouson = InStr(Range("E" & sikutyousonCnt).Value, "村")
            Range("F" & sikutyousonCnt).Value = Left(Range("E" & sikutyousonCnt).Value, sikutyouson)
            Range("G" & sikutyousonCnt).Value = mid(Range("E" & sikutyousonCnt).Value, sikutyouson + 1)
        End If
    Next
    
End Sub

長いコードですが内容は問題ないはずです。

都道府県の部分が市区町村になっただけです。

実行して以下のようになったら成功です。

成功はしましたが、少し見栄えが悪いので修正を加えましょう。

E列がF・G列のデータと重複しているので、非表示にします。

非表示にするコードはこちらです。

Columns("E").Hidden = True

このコードを先ほどのプロシージャに付け足しておくと見た目もすっきりした資料が出来上がります。

最後に今回作成したプロシージャ２つを統合してまとめましょう。

Sub todoufuken_kansei()
    Call todoufuken_kugiri
    Call sikutyouson_kugiri
End Sub

こんな感じです。

これで完成です。お疲れさまでした。

まとめ

ここまでデータを区切って表示する方法をご紹介しましたが、データは初めからできるだけ細かく区切っておくことをおススメします。

今回でいうと都道府県と市区町村と市区町村以下に分けて初めから作っておけばデータとしての汎用性は高く、加工する手間が省けるため便利です。

逆に細かく分けて作っていおいて、場面によって結合するほうが実用的でしょう。

今後は、結合する方法もそうですが、任意の文字列で検索する方法なども今後ご紹介できればと思っていますのでお楽しみに。

※条件分岐でIf文を使いましたがVBAにはSelectcaseを使う方法もあるので余裕のある方はチャレンジしてみましょう。