連載
» 2015年04月23日 05時00分 UPDATE

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

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

[薬師寺国安,PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本Tipsの環境:Windows 8.1 Enterprise(64ビット)+Excel 2013


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


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

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

Join関数の書式

Join(list,delimiter)


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

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

ExcelVBAkansu6Tips_01.png 図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だ。

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

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

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

InStr関数の書式

InStr(Start,String1,String2,Compare)


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

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

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

表1 Compareに指定する値
定数 説明
vbBinaryCompare バイナリモードで比較する
vbTextCompare テキストモードで比較する
参考「InStr 関数

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

ExcelVBAkansu5Tips_01.png 図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だ。

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

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

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

InStrRev関数の書式

InStrRev(String1,String2,Start,Compare)


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

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

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

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

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

ExcelVBAkansu5Tips_03.png 図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だ。

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

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

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

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

 Replace(expression,find,replacewith)


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

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

ExcelVBAkansu9Tips_03.png 図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だ。

ExcelVBAkansu9Tips_04.png 図8 置換が実行された

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

まとめ

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

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

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

著者紹介

薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所

kuniyasu.jpg

薬師寺国安事務所代表。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© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。