Stripping Trailing Letters and Periods

Chris has data in a worksheet that consists, in column A, of text values, such as 5.552A.P or 8X.345.C. There is no pattern within the data, other than values will always end with some combination of periods and letters (A.P and .C in the examples). Chris needs to strip off these trailing characters and return what is before them. He wonders if there is a way to perform this task using a formula.

This is a tricky one, as Chris essentially wants to remove any letters and any periods that may occur after the last digit in a text string. The first thought is that, perhaps, the task can be accomplished by figuring out where the last digit is in the text string and then deleting everything after that. However, Chris said that only trailing letters and periods should be deleted. That means that the string "T54.X;" should not have anything deleted because the last character (the semicolon) should not be deleted, and thus everything before it should remain.

The complexity (or trickiness, if you prefer) means that the best solution for the greatest number of Excel versions will be achieved by using a macro. Before getting to a macro solution, however, consider the following, which will work in Excel 365:

=REGEXREPLACE(A2, "[A-Za-z.]+$", "")

The REGEXREPLACE function uses a pattern to determine what to replace in cell A2. In this case, the pattern is "any number of letters and periods that occur at the end of the text string." The [A-Za-z.] indicates the matchable characters (letters and periods), the plus sign (+) means "any number of the foregoing characters," and the dollar sign ($) means "at the end of the text string." Anything that matches this pattern is replaced with an empty string (""), meaning it is deleted. What is left is what Chris indicates he needs, with no need for a macro.

Which brings us to the macro approach. You can create a user-defined function that will do essentially the same thing:

Function StripSuffix(ByVal Value As String) As String
    Dim J As Long
    Dim ch As String

    For J = Len(Value) To 1 Step -1
        ch = Mid(Value, J, 1)
        If Not (ch Like "[A-Za-z.]") Then
            StripSuffix = Left(Value, J)
            Exit Function
        End If
    Next J
    StripSuffix = ""
End Function

The UDF steps backward through the contents of the cell and, at the first occurrence of something that is not a period or letter, exits the function and returns that truncated string. If the cell is composed of nothing but letters and periods, then the UDF returns an empty string, which is the exact behavior of the REGEXREPLACE function. To use the UDF in your worksheet, use the following:

=StripSuffix(A2)

While the REGEXREPLACE formula requires Excel 365, the UDF approach will work just fine in all modern versions of Excel.

There is one thing to note as a potential gotcha: When I was doing my testing, I typed in the text "ABC....." and expected the REGEXREPLACE formula and the UDF to both return an empty string. They did not. The reason is that when I typed the first three periods, Excel automatically converted them to an ellipsis character—they looked like periods, but were not periods. The string, without my notice, turned into "ABC" followed by an ellipsis character and two additional periods. So, the ellipsis was left in the text string returned by both the formula and the UDF.

The bottom line is that if you don't get the precise results you expect, then you may want to examine what Excel is doing with what you are providing to it.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (14003) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.