Excelで文字列を結合、検索、置換する関数の使い方――Join、InStr、InStrRev、Replace：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、配列の各要素を結合して文字列を作成するJoin関数、ある文字列の中から指定した文字列を検索し位置を返すInStr／InStrRev関数、指定された文字列の一部を別の文字列で置換して返すReplace関数について。

[薬師寺国安，PROJECT KySS]

連載目次

※本Tipsの環境：Windows 8.1 Enterprise（64ビット）＋Excel 2013

　今回のTipsも関数の使い方について解説する。今回は、「Join」「InStr」「InStrRev」「Replace」という「文字列」に関する4つ関数の使い方だ。なお、関数の基本的な使い方については、Tips「コンパイルエラーにならない関数の使い方――括弧の有無、複数の引数、Callステートメント、戻り値、名前付き引数と順番」を参照してほしい。

• 配列の各要素を結合して文字列を作成するJoin
• ある文字列の中から指定した文字列を検索し、開始位置を返すInStr
• ある文字列の中から指定した文字列を最後の文字位置から検索し、位置を返すInStrRev
• 指定された文字列の一部を別の文字列で置換して返すReplace

配列の各要素を結合して文字列を作成するJoin

　「Join」関数は、配列に含まれる各要素の内部文字列を結合して、作成される文字列を返す関数だ。以前のTipsで要素ごとに区切られた文字列から、ゼロベースの1次元配列を作成するSplit関数を紹介したが、その逆の関数といえるだろう。

Join関数の書式

Join(list,delimiter)

　「list」は必須項目で、結合する文字列を含む一次元配列を指定する。「delimiter」は省略可能で、戻り値となる文字列を区切るのに使用する文字を指定する。例えば「,（カンマ）」を指定すると、カンマで区切られて表示される。省略すると、半角スペースが使用される。

　図1のように「住所」や「氏名」が異なったセルに入力されており、「結合」ボタンがあったとする。

図1　「住所」や「氏名」が異なったセルに入力されており、「結合」ボタン配置されている

　各セルにはExcelのメニューから「書式」を設定している。読者の皆さんが好きなデザインにしても構わない。

　「結合」ボタンをクリックすると、異なったセルに入力された「住所」や「氏名」が結合され、セル「C7」に表示されるマクロはリスト1になる。

Option Explicit
Sub Join関数の使い方()
  Dim myArray(4) As String
  myArray(0) = Range("B2").Value
  myArray(1) = Range("B3").Value
  myArray(2) = Range("B4").Value
  myArray(3) = Range("B5").Value
  myArray(4) = Range("B6").Value
  Range("C7").Value = Join(myArray)
End Sub

リスト1　異なったセルに入力された「住所」や「氏名」が結合されて表示されるマクロ

　配列の添え字が「4」の、文字列型の配列変数「myArray」を宣言する（3行目）。各配列に「住所」「氏名」が入力されているセルの値を代入する（4〜8行目）。

　「Join」関数を使って、セル「C7」に全てを連結して表示する（9行目）。「delimiter」を省略しているので、各文字列の間はスペースで区切られている。

　VBE（Visual Basic Editor）を起動してメニュー［挿入］→［標準モジュール］を選択し、プロジェクト内に追加されるModule1内にリスト1のマクロを記述する。このリスト1のマクロを「結合」ボタンに関連付け実行した結果が図2だ。

図2　「Join」関数で文字列が結合されて表示された

ある文字列の中から指定した文字列を検索し、開始位置を返すInStr

　「InStr」関数は、ある文字列の中から指定した文字列を検索し、最初に見つかった文字列の開始位置を返す関数だ。

InStr関数の書式

InStr(Start,String1,String2,Compare)

　「Start」は省略可能で、検索の開始位置を設定する数式を設定する。省略すると文字の先頭から検索する。開始位置のインデックスは「1」から始まる。

　「String1」は必須項目で、検索先の文字列を指定する。「String2」も必須項目で、検索する文字列を指定する。

　「Compare」は省略可能で、文字列比較のタイプを指定する。表1の値を指定する。Compareを省略するとバイナリモードで比較される。

表1　Compareに指定する値

定数	説明
vbBinaryCompare	バイナリモードで比較する
vbTextCompare	テキストモードで比較する
参考「InStr 関数」

　図3のように「検索文字」を入力するセルと「チェック」ボタンがあり、その下に「氏名」のデータがあったとする。

図3　「検索文字」を入力するセルと「チェック」ボタンがあり、その下に「氏名」のデータがある

　各セルにはExcelのメニューから「書式」を設定している。読者の皆さんが好きなデザインにしても構わない。

　「チェック」ボタンをクリックして、該当する「氏名」に「●」が付くマクロはリスト2になる。

Option Explicit
Sub Instr関数の使い方()
  Dim 文字位置 As Integer
  Dim i As Integer
  If Range("C2").Value = "" Then
    MsgBox "検索文字を入力。"
    Exit Sub
  Else
    For i = 4 To 12
      文字位置 = InStr(1, Cells(i, 2), Range("C2").Value)
      If 文字位置 > 0 Then
        Cells(i, 3) = "●"
      Else
        Cells(i, 3) = "×"
      End If
    Next
  End If
End Sub

リスト2　指定した文字列の含まれる「氏名」を検索するマクロ

　Integer型の「文字位置」変数と「i」変数を宣言する（3〜4行目）。

　セル「C2」が未入力の場合はメッセージを表示して処理を抜ける（5〜7行目）。それ以外の場合は、9〜16行目の処理を行う。

　変数「i」を「4」から「12」まで繰り返す（9〜16行目）。この数字は「行番号」に該当する。繰り返しの過程で以下の処理を行う。

　「Instr」関数で、「i」行目の「2列目（B列）」の中で、セル「C2」に入力された文字の、文字位置を取得する（10行目）。

　変数「文字位置」が0より大きかった場合は、該当する文字が見つかったことになるので、その行の「C列」に「●」を付ける（13行目）。それ以外には「×」を表示する（14行目）。

　VBEを起動して［挿入］から［標準モジュール］を選択し、プロジェクト内に追加されるModule1内にリスト2のマクロを記述する。このリスト2のマクロを「チェック」ボタンに関連付け、実行した結果が図4だ。

図4　Instr関数で指定した文字を探し「●」を付けた

ある文字列の中から指定した文字列を最後の文字位置から検索し、位置を返すInStrRev

　「InStrRev」関数は、ある文字列の中から指定された文字列を、最後の文字位置から検索を開始し、最初に見つかった文字位置を返す文字列処理関数だ。

InStrRev関数の書式

InStrRev(String1,String2,Start,Compare)

　「String1」は必須項目で、検索先の文字列を指定する。

「String2」は必須項目で、検索する文字列を指定する。

「Start」は省略可能で、検索開始位置の数式を指定する。省略すると-1が使用され、最後の文字位置から検索される。

「Compare」は省略可能で、文字を比較する場合のモードを指定する。表1参照。

　図5のようにフルパス付きファイル名の表示されたセルと「ファイル名」のみを表示させるセルと「ファイル名のみ取得」というボタンがあったとする。

図5　フルパス付きファイル名と「ファイル名のみ取得」ボタンが配置されている

　「InStrRev」関数で「ファイル名」だけを取得するマクロはリスト3になる。

Sub InStrRev関数の使い方()
  Dim ファイル位置 As Integer
  Dim ファイルパス As String
  Dim ファイル名 As String
  ファイルパス = Range("H4").Value
  ファイル位置 = InStrRev(ファイルパス, "\")
  ファイル名 = Right(ファイルパス, Len(ファイルパス) - ファイル位置)
  Range("H5").Value = ファイル名
End Sub

リスト3　「InStrRev」関数で「ファイル名」だけを取得するマクロ

　Integer型の「ファイル位置」変数をと文字列型の「ファイルパス」と「ファイル名」変数を宣言する（2〜4行目）。

　変数「ファイルパス」には、セル「H4」の内容を格納しておく（5行目）。

　変数「ファイル位置」には、「InStrRev」関数で変数「ファイルパス」に格納されている値の最後から「\」の位置を取得して、格納しておく（6行目）。

　変数「ファイル名」には、「Right」関数を使って、変数「ファイルパス」の長さから、変数「ファイル位置」の格納している値を差し引いて、残った右側の値を取得して格納する（7行目）。つまり、変数「ファイルパス」には「66」の値が格納され、変数「ファイル位置」には「42」の値が格納される。「66-42＝24」となり、右側から24文字分取り出すとファイル名だけが取得できる、というわけだ。

　最後に、セル「H5」に変数「ファイル名」の値を表示する。

　リスト3のマクロを「ファイル名のみ取得」ボタンに関連付け、実行した結果が図6だ。

図6　「InStrRev」関数を使って「ファイル名」だけが表示された

指定された文字列の一部を別の文字列で置換して返すReplace

　「Replace」関数は、指定された文字列の一部を、別の文字列で置換して返す関数だ。

Replace関数の書式（※省略可能な引数は省略している）

　Replace(expression,find,replacewith)

　「expression」は必須項目で、置換する文字列を含む文字列を指定する。「find」は必須項目で、検索する文字列を指定する。「replacewith」は必須項目で、置換する文字列を指定する。

　図7のように、「置換対象文字列」「検索文字列」「置換後の文字列」を入力するセルと、「結果」を表示するセルと「実行」ボタンを配置しておくとしよう。

図7　「置換対象文字列」「検索文字列」「置換後の文字列」を入力するセルと、「結果」を表示するセルと「実行」ボタンを配置

　任意の文字列を置換するマクロはリスト4になる。

Sub Replace関数の使い方()
  If Range("C3").Value = "" Or Range("C4").Value = "" Or Range("C5").Value = "" Then
    MsgBox "未入力箇所があります。"
    Exit Sub
  Else
    Range("C6").Value = Replace(Range("C3").Value, Range("C4").Value, Range("C5").Value)
  End If
End Sub

リスト4　任意の文字列を置換するマクロ

　セル「C3」「C4」「C5」が未入力だった場合は、警告メッセージを表示して処理を抜ける（2〜4行目）。それ以外は以下の処理を行う（5〜7行目）。

　セル「C6」に「Replace」関数を使って、検索した文字列を指定した文字列に置換して表示する（6行目）。

　リスト4のマクロを「実行」ボタンに関連付け、実行した結果が図8だ。

図8　置換が実行された

　「暑い」という文字列を「猛暑」という文字列に置換して、結果に表示された。

まとめ

　今回紹介した関数は、かなり使い道のある関数だと思う。「InStr」関数は、検索処理でもよく使用される関数だ。また「InStrRev」関数は、「ファイル名」だけを取得したい場合には力を発揮する。

　「Replace」関数はVBAでも結構多用される関数ではないかと思う。任意の文字列式から任意の文字列を検索して、指定された文字列に置換する関数であるから、VBAで、あるデータの一部を、別な値に一括置換する場合には力を発揮する。ぜひアレンジして、日常業務で利用してもらいたい。

　次回も引き続き、関数について解説する予定だ。お楽しみに。

著者紹介

薬師寺 国安（やくしじ くにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev （Oct 2003-Sep 2012）。

Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。

Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。

Microsoft MVP for Development Platforms-Windows Platform Development (Oct 2014-Sep 2015）。