Excelで特定の文字以降を削除する方法！関数とVBAで複数一括処理も！

Excelで特定の文字以降を削除させたいときはないでしょうか。

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

ですよね。

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

についてまとめます！

Excelで特定の文字以降を削除する

Excelで特定の文字以降を削除する方法について説明をします。

今回のサンプルデータは以下の通り、セルに入力された住所から、都道府県名だけを抽出していきます。

まずは特定文字を”県”に設定し、市町村以下の文字を削除していきます。

後半では「都道府」も対象となるよう複数条件を指定し、都道府県すべての住所を処理していきます。

関数その1：LEFT関数とFIND関数で特定文字以降を削除する（県のみ抽出）

LEFT関数とFIND関数を組み合わせ特定文字以降を削除する方法となります。
FIND関数は検索対象文字を探し当て、どこの位置に存在するか返す関数で、
削除文字の開始位置を取得するために利用します。

続いてLEFT関数は左前方から指定文字数までを返す関数となります。FIND関数で出た削除開始位置を末尾に指定することにより左から削除前の文字列に絞り込むことができます。

計算式は以下の通りです。

今回変換対象のデータはB2以下ですので、＜対象セル＞をB2に変更していきます。

D5までドラッグします。

できましたね。

エラー「#VALUE」を修正する

よく見ると「東京都」の行でエラー「#VALUE」が表示されていますね。
これはFIND関数で一致する件数がなく、0という結果となり、LEFT関数がどこまでを抽出すればよいか判断できないためです。

エラー「#VALUE」とならないようにするためには、FIND関数でマッチしない場合の例外条件を追加します。

IF分を追加し、おなじみCOUNTIF関数で県を含むか判定し、ない場合はそのまま値を表示させます。

エラー「#VALUE」とならなくなりましたね。

関数その2：MID関数とFIND関数で特定文字以降を削除する（県のみ抽出）

LEFT関数をMID関数に変更し、FIND関数を組み合わせ特定文字以降を削除する方法となります。
MID関数はその名の通り取得開始位置の中間指定が可能な関数です。

開始位置に1を指定することによりLEFT関数と同じ開始位置となり、FIND関数で出た削除開始位置を末尾に指定することにより左から削除前の文字列に絞り込むことができます。

計算式は以下の通りです。

今回変換対象のデータはB2以下ですので、＜対象セル＞をB2に変更していきます。

D5までドラッグします。

できましたね。

エラー「#VALUE」を修正する

「東京都」の行でエラー「#VALUE」とならないように以下のようにします。

エラー「#VALUE」とならなくなりましたね。

関数その3：複数の特定文字を条件に追加する（都道府県すべて抽出）

これまで「県」を対象に市町村以下を削除してきましたが、都道府も対象となるように変更しましょう。

仕様としまして、都道府県の内訳は1都1道2府43県となりますので、「東京都」と「北海道」はLEFT関数を使わずそのまま表示、「府」は「県」と同じくFIND関数を使い処理していきます。

文字抽出処理にはLEFT関数を使用していますが、抽出対象文字列が中間位置にある場合など、用途に応じてMID関数に置き換えてもらえばと思います。

「東京都」の条件を追加する

「都」は「東京都」一つだけですので、「東京都」が含まれていたら「東京都」だけを返すよう条件を追加します。

入力します。

東京都のみの表示になりましたね。

「府」の条件を追加する

続いて「府」の条件を追加しましょう。

「府」は複数ありますので、「県」と同じくFIND関数で処理していきます。

「東京都」でない場合の例外の箇所に、「府」の判定処理を追加していきます。

できましたね。

「東京都府中市」も「府」がありますが、「東京都」の処理を先にしたことにより「棟教徒府」となることを回避しています。

「北海道」の条件を追加する

最後に「北海道」の条件を「府」の判定の例外の箇所へ追加しましょう。

できましたね。

タカヒロ

かなり計算式が長くなりました…

VBAで特定の文字以降を削除する

VBAその1：一種類の特定の文字以降を削除する（県のみ抽出）

続いてVBAで特定の文字以降を削除してみましょう。

B2からB8まで入力されている値を取得し、「県」を含む場合はそれ以降の文字を削除の上、D列へ結果を返します。

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

Sub 一種類の特定文字以降を削除する()

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

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

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

'配列に格納された値すべてを対象に処理します。
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
        
            Cells(i + 1, j + 3) = Left(strTemp(i, j), InStr(strTemp(i, j), strKeyword))
            
        Else
        
            Cells(i + 1, j + 3) = strTemp(i, j)
            
        End If
    Next
Next

End Sub

「県」に該当する住所は都道府県のみの抽出ができましたね。
なお、「県」ではない住所はそのまま表示されます。

VBAその2：多種類の特定の文字以降を削除する（都道府県すべて抽出）

これまでは検索キーとなる特定文字は「県」の1種類のみでしたが、複数の特定文字を対象にし、都道府県すべてを対象にするようにしてみます。

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

Sub 特定文字以降を削除する_複数キー指定()

Dim strTemp As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim strKeywords As Variant
Dim arrTemp As Variant

'検索キーを指定します。
strKeywords = Array("県", "府", "東京都", "北海道")

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

'配列に格納された値すべてを対象に処理します。
For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    For j = LBound(strTemp, 2) To UBound(strTemp, 2)
    
         '末尾が指定文字であるか判定します
        For Each arrTemp In strKeywords
        
              If InStr(strTemp(i, j), arrTemp) Then
              
                  Cells(i + 1, j + 3) = Left(strTemp(i, j), InStr(strTemp(i, j), arrTemp) + Len(arrTemp) - 1)
                    '検索キーの文字が複数であっても1文字しか返らないため、Len(arrTemp) - 1を追加しキーすべての文字が返るようにしています。
              End If
        Next
    Next
Next

End Sub

はい、できましたね。

VBAで複数列にある多種類の特定の文字以降を削除する（都道府県すべて抽出）

これまで元情報は1列のみでしたが、サンプルコードでは複数列の範囲指定をすることができます。

サンプルでは”B2:C8″をレンジ指定し、2列同時に処理をしています。

以下を変更します。

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

Sub 特定文字以降を削除する_複数列複数キー指定()

Dim strTemp As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim strKeywords As Variant
Dim arrTemp As Variant

'検索キーを指定します。
strKeywords = Array("県", "府", "東京都", "北海道")

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

'配列に格納された値すべてを対象に処理します。
For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    For j = LBound(strTemp, 2) To UBound(strTemp, 2)
    
         '末尾が指定文字であるか判定します
        For Each arrTemp In strKeywords
        
              If InStr(strTemp(i, j), arrTemp) Then
              
                  Cells(i + 1, j + 4) = Left(strTemp(i, j), InStr(strTemp(i, j), arrTemp) + Len(arrTemp) - 1)
                    '検索キーの文字が複数であっても1文字しか返らないため、Len(arrTemp) - 1を追加しキーすべての文字が返るようにしています。
              End If
        Next
         

    Next
Next

End Sub

はい、できましたね！

タカヒロ

VBAは関数をセルへ入力する方式より、動作が重くならないのがよいですね。
対象が多い場合はVBAで処理をしたほうがよいでしょう。

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

さいごに

いかがでしょうか。

今回は、

についてまとめました。

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