Excel VBA

Excelで特定の文字に囲まれた文字を抽出・削除する方法！関数他、VBAで切り出す方法も！

Excelで特定の文字に囲まれた文字を抽出・削除させたいときはないでしょうか。

けど、そんな中で悩むことは、

・Excelで特定の文字に囲まれた文字を抽出・削除するやり方がわからない。
・関数やVBAで効率的に文字列を切り出ししたいがやり方がわからない。

ですよね。

今回はそんなお悩みを解決する

・関数で特定の文字に囲まれた文字を抽出・削除する
・VBAで特定の文字に囲まれた文字を抽出・削除する

についてまとめます！

関数で特定の文字に囲まれた文字を抽出・削除する

Excelで特定の文字に囲まれた文字を抽出・削除する方法について説明をします。
方法は左先方から特定文字の位置を判定し、一つ目と二つ目の位置番号を割り出します。

次に特定文字一つ目の位置番号と二つ目の番号から抽出する文字列の文字数を算出し、抽出の場合はMID関数、削除の場合はREPLACE関数を使用し処理していきます。

今回のサンプルデータは以下の通りです。

電話番号形式の値を例に「-」で囲まれた赤字部分を抽出・削除していきます。

関数その1：特定の文字に囲まれた文字を抽出する

MID関数で取得した特定の文字の位置番号から開始位置と抽出文字列の文字数を指定し抽出する方法となります。

MID関数はその名の通り取得開始位置の中間指定が可能な関数です。

MID関数の構文は以下の通りです。

MID(文字列, 開始位置, 文字数)

サンプルではB2に値、C2に特定文字である「-」を入力しています。
1個目の特殊文字の位置番号を取得するためFIND関数を以下のように設定します。

= FIND(C2,B2)

3が表示されましたね。
「00-」の文字数がカウントされた形になります。

タカヒロ
タカヒロ
半角、全角などバイト数に関係なく1文字1カウントされます。

続いて2個目の特定文字の位置番号を取得します。

FIND関数の3つ目のパラメータは検索開始位置を指定できます。

そこにFIND関数で出した1個目の文字列の位置番号に+1した数式を入力し、1つ目以降の文字列を検索対象とします。

そこでマッチした番号が2つ目の位置番号という形になります。

=FIND(C2,B2, FIND(C2,B2)+1)

タカヒロ
タカヒロ
FIND関数を入れ子にすると2つ目、3つ目の位置番号が取得できるというわけですね。

仕上げにMID関数で対象文字列を抽出します。

MID関数の構文は以下の通りです。

MID(文字列, 開始位置, 文字数)

 

抽出開始位置は1つ目の特定文字の次からになりますので、+1しています。
文字数は、2個目の特手文字の位置番号から1個目の位置番号を引いて-1した数式を入力します。

=MID(B2,D2+1,E2-D2-1)

結果を確認しましょう。

はい、「-」で囲まれた文字列「1111」だけが抽出されましたね。

関数その2：特定の文字に囲まれた文字を削除する

REPLACE関数で取得した特定の文字の位置番号から開始位置と削除文字列の文字数を指定し空文字””と置き換え削除する方式となります。

サンプルではB3に値、C3に特定文字である「-」を入力し、赤字部分を削除していきます。

1個目および2個目の位置番号取得方法は抽出と同様ですので、割愛します。
D1、D2それぞれドラッグして数式をコピーしてください。

REPLACE関数の構文は以下の通りで、MID関数と同様に各セルで算出した結果を割り当てていきます。

REPLACE(文字列,開始位置,文字数,置換文字)

置換開始位置は1つ目の特定文字の次からになりますので、+1しています。文字数は、2個目の特手文字の位置番号から1個目の位置番号を引いて-1した数式を入力します。

確認してみましょう。

はい、「-」で囲まれた文字列だけが削除され「00–2222」となりましたね。

 

VBAで特定の文字に囲まれた文字を抽出・削除する

VBAその1：特定の文字に囲まれた文字を抽出する

VBAで特定の文字に囲まれた文字を抽出してみましょう。

B2からB7まで入力されている値を取得し、 「-」の文字位置を判定し、その間の文字を抽出、D列へ結果を返します。

以下サンプルコードです。

Sub 特定の文字に囲まれた文字を抽出する()

Dim strTemp As Variant
Dim i As Integer
Dim j As Integer
Dim strKeyword As String
Dim intKaishi As Integer
Dim intMozisu As Integer

'検索キーを指定します。
strKeyword = "-"

'処理対象のセルをレンジ指定します。
strTemp = Range("B2:B7")

'配列に格納された値すべてを対象に処理します。
For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    For j = LBound(strTemp, 2) To UBound(strTemp, 2)
    
         '末尾が指定文字であるか判定します
        If InStr(strTemp(i, j), strKeyword) Then
        
            'キー１つ目の位置番号を取得します
            intKaishi = InStr(strTemp(i, j), strKeyword) + 1
            
            'キー２つ目の位置番号から1つ目の位置番号をマイナスし、抽出文字数を算出します
            intMozisu = InStr(intKaishi, strTemp(i, j), strKeyword) - intKaishi
                
            'MID関数で対象文字列を抽出します
            Cells(i + 1, j + 3) = Mid(strTemp(i, j), intKaishi, intMozisu)
            
        Else
        
            Cells(i + 1, j + 3) = strTemp(i, j)
            
        End If
    Next
Next

End Sub

検索キーを指定します。

strKeyword = “-“

値がある対象レンジ”B2:B7″を変数に指定します。

strTemp = Range(“B2:B7”)

二次元配列に格納された値すべてを対象に処理します。

For i = LBound(strTemp, 1) To UBound(strTemp, 1)
For j = LBound(strTemp, 2) To UBound(strTemp, 2)

‘キー１つ目の位置番号を取得します。FIND関数ではなく、InStr関数を使います。

intKaishi = InStr(strTemp(i, j), strKeyword) + 1

‘キー２つ目の位置番号から1つ目の位置番号をマイナスし、抽出文字数を算出します。

ntMozisu = InStr(intKaishi, strTemp(i, j), strKeyword) – intKaishi

‘MID関数で対象文字列を抽出します。

Cells(i + 1, j + 3) = Mid(strTemp(i, j), intKaishi, intMozisu)

実行してみましょう。

「-」で囲まれた部分のみ抽出できました。
「-」がない場合はそのまま表示されます。

VBAその2：特定の文字に囲まれた文字を削除する

次にVBAで特定の文字に囲まれた文字を削除してみましょう。

B2からB7まで入力されている値を取得し、 「-」の文字位置を判定し、その間の文字を削除、D列へ結果を返します。

以下サンプルコードです。

Sub 特定の文字に囲まれた文字を削除する()

Dim strTemp As Variant
Dim i As Integer
Dim j As Integer
Dim strKeyword As String
Dim intKaishi As Integer
Dim intOwari As Integer

'検索キーを指定します。
strKeyword = "-"

'処理対象のセルをレンジ指定します。
strTemp = Range("B2:B7")

'配列に格納された値すべてを対象に処理します。
For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    For j = LBound(strTemp, 2) To UBound(strTemp, 2)
    
         '末尾が指定文字であるか判定します
        If InStr(strTemp(i, j), strKeyword) Then
        
            'キー1つ目の位置番号を取得します
            intKaishi = InStr(strTemp(i, j), strKeyword) + 1

            'キー2つ目の位置番号を取得します
            intOwari = InStr(intKaishi, strTemp(i, j), strKeyword)

            'Left関数で1つ目より前の対象文字列を、Right関数で2つ目以降の文字列を取得し結合した結果を返します
            Cells(i + 1, j + 3) = Left(strTemp(i, j), intKaishi - 1) & Right(strTemp(i, j), Len(strTemp(i, j)) - intOwari + 1)
            
        Else
        
            Cells(i + 1, j + 3) = strTemp(i, j)
            
        End If
    Next
Next

End Sub

検索キーを指定します。

strKeyword = “-“

値がある対象レンジ”B2:B7″を変数に指定します。

strTemp = Range(“B2:B7”)

二次元配列に格納された値すべてを対象に処理します。

For i = LBound(strTemp, 1) To UBound(strTemp, 1)
For j = LBound(strTemp, 2) To UBound(strTemp, 2)

キー１つ目の位置番号を取得します。FIND関数ではなく、InStr関数を使います。

intKaishi = InStr(strTemp(i, j), strKeyword) + 1

キー2つ目の位置番号を取得します

intOwari = InStr(intKaishi, strTemp(i, j), strKeyword)

Left関数で1つ目より前の対象文字列を、Right関数で2つ目以降の文字列を取得し結合した結果を返します

Cells(i + 1, j + 3) = Left(strTemp(i, j), intKaishi – 1) & Right(strTemp(i, j), Len(strTemp(i, j)) – intOwari + 1)

タカヒロ
タカヒロ
Substitute関数で削除する方法もありますが「-####-」で検索・置換する必要があります。※strDelwordに囲いの値を代入します。
‘Cells(i + 1, j + 3) = WorksheetFunction.Substitute(strTemp(i, j), strKeyword & strDelword & strKeyword, strKeyword & strKeyword, 1)またReplace関数でも同じ要領でできますが、2つ目から3つ目の囲いの値の対応はできません。
‘Cells(i + 1, j + 3) = Replace(strTemp(i, j), strKeyword & strDelword & strKeyword, strKeyword & strKeyword, 1, 1)

実装して実行してみましょう。

「-」で囲まれた部分が削除できました。
「-」がない場合はそのまま表示されます。

＜追加＞VBAで特定の文字に囲まれた文字を抽出・削除した結果を配列に格納し一括貼り付けする

VBAで特定の文字に囲まれた文字を抽出・削除した結果を配列に格納し一括貼り付けをする方法について説明をします。

タカヒロ
タカヒロ
配列からの一括貼り付けにすることにより、処理の高速化が見込めますね。

VBAその1：特定の文字に囲まれた文字を抽出、配列に格納し一括貼り付けする

特定の文字に囲まれた文字を抽出、配列に格納し一括貼り付けするようにするため、以下の追加、変更をおこないます。

サンプルコードは以下の通りです。

Sub 特定の文字に囲まれた文字を抽出する_配列出力版()

Dim strTemp As Variant
Dim strTemp2 As Variant
Dim i As Integer
Dim j As Integer
Dim strKeyword As String
Dim intKaishi As Integer
Dim intMozisu As Integer

'検索キーを指定します。
strKeyword = "-"

'処理対象のセルをレンジ指定します。
strTemp = Range("B2:B7")
strTemp2 = Range("D2:D7")

'配列に格納された値すべてを対象に処理します。
For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    For j = LBound(strTemp, 2) To UBound(strTemp, 2)
    
         '末尾が指定文字であるか判定します
        If InStr(strTemp(i, j), strKeyword) Then
        
            'キー１つ目の位置番号を取得します
            intKaishi = InStr(strTemp(i, j), strKeyword) + 1
            
            'キー２つ目の位置番号から1つ目の位置番号をマイナスし、抽出文字数を算出します
            intMozisu = InStr(intKaishi, strTemp(i, j), strKeyword) - intKaishi
                
            'MID関数で対象文字列を抽出します
            strTemp2(i, j) = Mid(strTemp(i, j), intKaishi, intMozisu)
            
        Else
        
            strTemp2(i, j) = strTemp(i, j)
            
        End If
    Next
Next

'配列からセルへ一括貼り付けをします
Range("D2:D7") = strTemp2

End Sub

追加、変更した箇所について説明をします。

配列用変数をVariant型で宣言します。

Dim strTemp2 As Variant

追加した配列に対し貼り付け先Rangeを代入します。
同時に二次元配列にすることができます。

また、Rangeの範囲指定は処理対象に合わせて変更をします。

strTemp2 = Range("D2:D7")

処理結果をCellsで直接値貼り付けしていたところをstrTemp2配列へ格納するようにします。

strTemp2(i, j) = Left(strTemp(i, j), intKaishi - 1) & Right(strTemp(i, j), Len(strTemp(i, j)) - intOwari + 1)
Else
strTemp2(i, j) = strTemp(i, j)

配列からセルへ一括貼り付けをします。
Rangeの範囲指定は処理対象に合わせて変更をします。

Range("D2:D7") = strTemp2

VBAその2：特定の文字に囲まれた文字を削除、配列に格納し一括貼り付けする

特定の文字に囲まれた文字を削除、配列に格納し一括貼り付けするサンプルコードは以下の通りです。

Sub 特定の文字に囲まれた文字を削除する_配列出力版()

Dim strTemp As Variant
Dim strTemp2 As Variant
Dim i As Integer
Dim j As Integer
Dim strKeyword As String
Dim intKaishi As Integer
Dim intOwari As Integer

'検索キーを指定します。
strKeyword = "-"

'処理対象のセルをレンジ指定します。
strTemp = Range("B2:B7")
strTemp2 = Range("D2:D7")

'配列に格納された値すべてを対象に処理します。
For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    For j = LBound(strTemp, 2) To UBound(strTemp, 2)
    
         '末尾が指定文字であるか判定します
        If InStr(strTemp(i, j), strKeyword) Then
        
            'キー1つ目の位置番号を取得します
            intKaishi = InStr(strTemp(i, j), strKeyword) + 1

            'キー2つ目の位置番号を取得します
            intOwari = InStr(intKaishi, strTemp(i, j), strKeyword)

            'Left関数で1つ目より前の対象文字列を、Right関数で2つ目以降の文字列を取得し結合した結果を返します
            strTemp2(i, j) = Left(strTemp(i, j), intKaishi - 1) & Right(strTemp(i, j), Len(strTemp(i, j)) - intOwari + 1)
            
        Else
        
            strTemp2(i, j) = strTemp(i, j)
            
        End If
    Next
Next

'配列からセルへ一括貼り付けをします
Range("D2:D7") = strTemp2

End Sub

タカヒロ
タカヒロ
修正箇所は抽出版と同じとなりますので、説明は割愛させていただきます。

VBAの実装手順

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。

さいごに

いかがでしょうか。

今回は、

・関数で特定の文字に囲まれた文字を抽出・削除する
・VBAで特定の文字に囲まれた文字を抽出・削除する

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。


「抽出」の関連記事

こちらの記事の内容「抽出」に関連する記事一覧です。







3 件のコメント

  • いつも、参考にさせて頂いております。早速ですが、今回のコードの値の貼り付け時に配列を使用して一括で貼り付けたいのですが、ご教示おねがいします。

    返信する

    • コメントを残す

    メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

    RECOMMENDこちらの記事も人気です。