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

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

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

・Excelで末尾の特定文字を削除するやり方がわからない。
・関数やVBAで効率的に行いたいがやり方がわからない。

ですよね。

文字処理は関数で1セルづつ行う方法とVBAで複数行、列を指定しまとめて処理を行う方法がありますので、やり方を覚えておくととても便利です!

今回はそんなお悩みを解決する
Excel関数とVBAで末尾の特定文字を削除する方法について
まとめます!

Excel関数で末尾の特定文字を削除する

まずはExcelの関数で末尾の特定文字を削除する方法について説明をします。
やり方は3種類ほどありますが、考え方は同じで、末尾の文字を判定し、あればその文字を取り除いた結果を返すというものです。

今回のサンプルデータと処理結果は以下の通りです。

削除対象となる文字は””で、末尾に存在する一文字だけを消去していきます。

なお、No.4の値のように末尾の文字が特定文字に該当しない場合はそのまま出力します。

では、順に説明していきます。

関数その1:REPLACE関数で末尾の特定文字を削除する

REPLACE関数で末尾の特定文字を削除する方法となります。
REPLACE関数はその名の通り指定文字を別の文字へ置き換える関数で、今回は特定文字を空文字””へ置き換えていきます。

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

=IF(RIGHT(<対象セル>,1)=”★”,REPLACE(<対象セル>,LEN(<対象セル>),1,””),<対象セル>)

RIGHT関数で右から一文字目、つまり末尾の文字を抽出し、特定文字”★”であるかどうか判定をします。
判定の結果一致していればREPLACE関数でLEN関数から取得した文字数の一文字目を空文字””へ置き換えます。

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

=IF(RIGHT(B2,1)=”★”,REPLACE(B2,LEN(B2),1,””),B2)

D5までドラッグします。

できましたね。

関数その2:LEFT関数で末尾の特定文字を削除する

LEFT関数で末尾の特定文字を削除する方法となります。
LEFT関数は左から指定番地までの文字を返す関数で、今回は最大文字数から-1して末尾の特定文字を削除していきます。

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

=IF(RIGHT(<対象セル>,1)=”★”,LEFT(<対象セル>,LEN(<対象セル>)-1),<対象セル>)

RIGHT関数で右から一文字目の特定文字”★”であるかどうか判定をします。
判定の結果一致していればLEFT関数でLEN関数から取得した文字数から一文字削除した文字を返します。

対象のデータはB2以下ですので、まずは<対象セル>をB2に変更していきます。

=IF(RIGHT(B2,1)=”★”,LEFT(B2,LEN(B2)-1),B2)

D5までドラッグします。

できましたね。

関数その3:RIGHT関数で末尾の特定文字を判定し、LEFT関数で末尾の特定文字を削除する

これまではIF分で特定文字の判定をしていましたが、RIGHT関数でも特定文字を判定することは可能ですので、IFの代用として使用してみます。

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

=LEFT(<対象セル>,LEN(<対象セル>)-((RIGHT(<対象セル>,1)=”★”))-0)

RIGHT関数に条件式を追記すると、 ブール型 (Boolean型ともいいTrueかFalseで返る) で結果が返ります。

=RIGHT(B2,1)=”★”

次にブール型を整数の0か1にするために演算式を追加し整数型へ変換します。

=(RIGHT(B2,1)=”★”)-0

RIGHT関数の結果が0(False)であればそのままの値、1(True)となり、

該当する場合はLEFT関数で最大文字数から-1して末尾の特定文字を削除していく形となります。

さっそく実装してみましょう。

対象のデータはB2以下ですので、まずは<対象セル>をB2に変更していきます。

=LEFT(B2,LEN(B2)-((RIGHT(B2,1)=”★”)-0))

D5までドラッグします。

できましたね。

タカヒロ
タカヒロ
計算式は他よりスリムになりますが、ブール型を整数型へ変換するやり方はあまり一般的でないかもしれません。

VBAで末尾の特定文字を削除する

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

B2からB5まで入力されている値を取得し、D列へ末尾を削除した結果を返します。

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

Sub 末尾の特定文字を削除する()

Dim strTemp As String
Dim n As Integer

'入力行分繰り返します
For n = 2 To 5

'置換対象の値を変数へ格納します
    strTemp = Range("B" & n).Text
    
    '末尾が指定文字であるか判定します
    If Right(strTemp, 1) = "★" Then
    
        Range("D" & n) = Left(strTemp, Len(strTemp) - 1)
        
    Else
    
        Range("D" & n) = strTemp
    
    End If
Next

End Sub

※VBAの実装は以下「VBAの実装手順」、実行方法は「VBAを実行する」をご参照ください。

できましたね。

VBAで複数列にある末尾の特定文字を削除する

これまでは1列にある値を対象にしていましたが、対象を複数列にし、より広範囲に処理していきます。

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

Sub 末尾の特定文字を削除する_範囲拡大版()

Dim strTemp As Variant
Dim i As Integer
Dim j As Integer

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

'配列に格納された値すべてを対象に処理します。
For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    For j = LBound(strTemp, 2) To UBound(strTemp, 2)
    
         '末尾が指定文字であるか判定します
        If Right(strTemp(i, j), 1) = "★" Then
        
            Cells(i + 1, j + 4) = Left(strTemp(i, j), Len(strTemp(i, j)) - 1)
            
        Else
        
            Cells(i + 1, j + 4) = strTemp(i, j)
            
        End If
    Next
Next

End Sub

※VBAの実装は以下「VBAの実装手順」、実行方法は「VBAを実行する」をご参照ください。

処理対象を”B2:C5″とし、結果を”E2:F5″へ返す処理となっています。

はい、できましたね。

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

今度は3列に増やして処理をしてみましょう。

処理対象を”B2:D5″とし、結果を”F2:H5″へ返す処理となっています。

VBAの赤字の箇所を修正します。

strTemp = Range(“B2:D5“)
Cells(i + 1, j + 5) = Left(strTemp(i, j), Len(strTemp(i, j)) – 1)
Cells(i + 1, j + 5) = strTemp(i, j)

3列用に訂正したサンプルコードです。

Sub 末尾の特定文字を削除する_範囲拡大版()

Dim strTemp As Variant
Dim i As Integer
Dim j As Integer

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

'配列に格納された値すべてを対象に処理します。
For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    For j = LBound(strTemp, 2) To UBound(strTemp, 2)
    
         '末尾が指定文字であるか判定します
        If Right(strTemp(i, j), 1) = "★" Then
        
            Cells(i + 1, j + 5) = Left(strTemp(i, j), Len(strTemp(i, j)) - 1)
            
        Else
        
            Cells(i + 1, j + 5) = strTemp(i, j)
            
        End If
    Next
Next

End Sub

実行してみましょう。

はい、問題なく反映されましたね。

タカヒロ
タカヒロ
列数によって出力先の列の位置番号を変える必要がありますが、変数で持たせてあげると、設定の際に楽になるでしょう。

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

さいごに

いかがでしょうか。

今回は、
Excelで末尾の特定文字を削除する方法について
まとめました。

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



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

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



タカヒロ

 
タカヒロ  
Excelの基礎がスルスル入ってくるAmazonランク1位の優良Excel本です。
 
この基礎本と応用技が多いエク短と組み合わせれば、短時間で誰よりもExcelが使いこなせるかと思います。
 
2022/1/21(金)23: 59までAmazon限定の「厳選ショートカットキー」DL特典アリです。 >詳細を見てみる 

コメントを残す

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