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.
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
