Pages

How to Remove the Last Full Stop from Every Cell in a Selection Using VBA

In today's post, I'm going to show you how to remove the last full stop from a cell in Excel using VBA. This can be especially useful when cleaning up text data. For example, if you have a list of sentences and you want to ensure that none of them end with a full stop, you can use this simple VBA code.

Let’s consider this example worksheet.

Sample sentences with full stop marks

Here's the complete code:

Sub RemoveLastFullStop()

Dim MyCell As Range
Dim trimmedSentence As String
Dim LastChar As String

For Each MyCell In Selection

    If MyCell.Value <> "" Then
    
        MyCell.Value = Trim(MyCell.Value)
        
        trimmedSentence = MyCell.Value
        
        LastChar = Right(trimmedSentence, 1)
        
        If StrComp(LastChar, Chr(46), vbBinaryCompare) = 0 Then
            MyCell.Value = Mid(trimmedSentence, 1, Len(trimmedSentence) - 1)
        End If
    End If
    
Next

MsgBox "Completed!", vbInformation, ""

End Sub

Below is the explanation of the above code.

Loop Through Each Cell in the Selection.
The code starts by looping through each cell in the selected range.

For Each MyCell In Selection

Check if the Cell is Not Empty.
Inside the loop, we check if the cell is not empty.

If MyCell.Value <> "" Then

Trim the Cell Value
We then use the Trim function to remove any leading or trailing spaces from the cell value.

MyCell.Value = Trim(MyCell.Value)

Get the Last Character
Next, we store the trimmed sentence in a variable and get the last character of the sentence.

trimmedSentence = MyCell.Value
LastChar = Right(trimmedSentence, 1)

Check if the Last Character is a Full Stop
We then check if the last character is a full stop using the StrComp function.

If StrComp(LastChar, Chr(46), vbBinaryCompare) = 0 Then

Here Chr(46) is used to represent the full stop (.) character.

This is the breakdown of what Chr(46) does:

Chr: This function returns the character associated with the specified character code.
46: This is the ASCII code for the full stop (.) character.
So, Chr(46) converts the ASCII code 46 into the corresponding character, which is a full stop.

If it is a full stop, we remove it by taking all characters except the last one.

MyCell.Value = Mid(trimmedSentence, 1, Len(trimmedSentence) - 1)

Display a Message Box

After the loop is done, we display a message box to indicate that the process is complete.

MsgBox "Completed!", vbInformation, ""

By following these steps, you can easily remove the last full stop from each cell in your selected range. This is the result you will get when run the above macro

Result after removing full stops.