Excelで文字列を結合、検索、置換する関数の使い方――Join、InStr、InStrRev、Replace：VBA／マクロ便利Tips
業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、配列の各要素を結合して文字列を作成するJoin関数、ある文字列の中から指定した文字列を検索し位置を返すInStr／InStrRev関数、指定された文字列の一部を別の文字列で置換して返すReplace関数について。
今回のTipsも関数の使い方について解説する。今回は、「Join」「InStr」「InStrRev」「Replace」という「文字列」に関する4つ関数の使い方だ。なお、関数の基本的な使い方については、Tips「コンパイルエラーにならない関数の使い方――括弧の有無、複数の引数、Callステートメント、戻り値、名前付き引数と順番」を参照してほしい。
- 配列の各要素を結合して文字列を作成するJoin
- ある文字列の中から指定した文字列を検索し、開始位置を返すInStr
- ある文字列の中から指定した文字列を最後の文字位置から検索し、位置を返すInStrRev
- 指定された文字列の一部を別の文字列で置換して返すReplace
配列の各要素を結合して文字列を作成するJoin
「Join」関数は、配列に含まれる各要素の内部文字列を結合して、作成される文字列を返す関数だ。以前のTipsで要素ごとに区切られた文字列から、ゼロベースの1次元配列を作成するSplit関数を紹介したが、その逆の関数といえるだろう。
Join関数の書式
Join(list,delimiter)
「list」は必須項目で、結合する文字列を含む一次元配列を指定する。「delimiter」は省略可能で、戻り値となる文字列を区切るのに使用する文字を指定する。例えば「,（カンマ）」を指定すると、カンマで区切られて表示される。省略すると、半角スペースが使用される。
図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
配列の添え字が「4」の、文字列型の配列変数「myArray」を宣言する（3行目）。各配列に「住所」「氏名」が入力されているセルの値を代入する（4〜8行目）。
「Join」関数を使って、セル「C7」に全てを連結して表示する（9行目）。「delimiter」を省略しているので、各文字列の間はスペースで区切られている。
VBE（Visual Basic Editor）を起動してメニュー［挿入］→［標準モジュール］を選択し、プロジェクト内に追加されるModule1内にリスト1のマクロを記述する。このリスト1のマクロを「結合」ボタンに関連付け実行した結果が図2だ。
ある文字列の中から指定した文字列を検索し、開始位置を返すInStr
「InStr」関数は、ある文字列の中から指定した文字列を検索し、最初に見つかった文字列の開始位置を返す関数だ。
InStr関数の書式
InStr(Start,String1,String2,Compare)
「Start」は省略可能で、検索の開始位置を設定する数式を設定する。省略すると文字の先頭から検索する。開始位置のインデックスは「1」から始まる。
「String1」は必須項目で、検索先の文字列を指定する。「String2」も必須項目で、検索する文字列を指定する。
「Compare」は省略可能で、文字列比較のタイプを指定する。表1の値を指定する。Compareを省略するとバイナリモードで比較される。
| 定数 | 説明 |
|---|---|
| vbBinaryCompare | バイナリモードで比較する |
| vbTextCompare | テキストモードで比較する |
| 参考「InStr 関数」 | |
図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
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だ。
ある文字列の中から指定した文字列を最後の文字位置から検索し、位置を返すInStrRev
「InStrRev」関数は、ある文字列の中から指定された文字列を、最後の文字位置から検索を開始し、最初に見つかった文字位置を返す文字列処理関数だ。
InStrRev関数の書式
InStrRev(String1,String2,Start,Compare)
「String1」は必須項目で、検索先の文字列を指定する。
「String2」は必須項目で、検索する文字列を指定する。
「Start」は省略可能で、検索開始位置の数式を指定する。省略すると-1が使用され、最後の文字位置から検索される。
「Compare」は省略可能で、文字を比較する場合のモードを指定する。表1参照。
図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
Integer型の「ファイル位置」変数をと文字列型の「ファイルパス」と「ファイル名」変数を宣言する（2〜4行目）。
変数「ファイルパス」には、セル「H4」の内容を格納しておく（5行目）。
変数「ファイル位置」には、「InStrRev」関数で変数「ファイルパス」に格納されている値の最後から「\」の位置を取得して、格納しておく（6行目）。
変数「ファイル名」には、「Right」関数を使って、変数「ファイルパス」の長さから、変数「ファイル位置」の格納している値を差し引いて、残った右側の値を取得して格納する（7行目）。つまり、変数「ファイルパス」には「66」の値が格納され、変数「ファイル位置」には「42」の値が格納される。「66-42＝24」となり、右側から24文字分取り出すとファイル名だけが取得できる、というわけだ。
最後に、セル「H5」に変数「ファイル名」の値を表示する。
リスト3のマクロを「ファイル名のみ取得」ボタンに関連付け、実行した結果が図6だ。
指定された文字列の一部を別の文字列で置換して返すReplace
「Replace」関数は、指定された文字列の一部を、別の文字列で置換して返す関数だ。
Replace関数の書式（※省略可能な引数は省略している）
Replace(expression,find,replacewith)
「expression」は必須項目で、置換する文字列を含む文字列を指定する。「find」は必須項目で、検索する文字列を指定する。「replacewith」は必須項目で、置換する文字列を指定する。
図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
セル「C3」「C4」「C5」が未入力だった場合は、警告メッセージを表示して処理を抜ける（2〜4行目）。それ以外は以下の処理を行う（5〜7行目）。
セル「C6」に「Replace」関数を使って、検索した文字列を指定した文字列に置換して表示する（6行目）。
リスト4のマクロを「実行」ボタンに関連付け、実行した結果が図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）。
Copyright © ITmedia, Inc. All Rights Reserved.
関連記事
スクショをExcelに張り付けるのに役立つ4つのテクニック
システム開発におけるソフトウェアテスト（結合テスト〜システムテスト）において重要視されるエビデンス（作業記録）。前後編の2回にわたって、エビデンスとしてスクリーンショットをキャプチャし、テスト仕様書や納品書に張り付けていく作業を自動化するためのVBA／マクロのテクニックを紹介する。後編は、画像ファイルをシートに張り付け、Excel 2013のメニューからスクショを直に張り付け、画像を縮小し、指定した時間にマクロを実行する方法を解説。
［Esc］キーによるExcel VBAの実行中断を防止する
ExcelではVBA（Visual Basic for Applications）によってさまざまな処理を自動的に実行できる。しかし、VBAの実行中にユーザーが［Esc］キーあるいは［Ctrl］＋［Break］キーを押すと、自動処理が止まってしまう。止めたくない場合は、Application.EnableCancelKeyプロパティの設定を変更する。- Windows TIPSディレクトリ > プラットフォーム別 > Office > Excel