Hi - Dave here.

Happy Friday!

We've been looking at some more complicated formulas recently, and this week I want to look at a simple problem. How can we remove characters from the end of a text string? 

As usual, there are many ways to do this in Excel. The classic traditional approach is a formula based on the LEFT function with the LEN function. You can see this in the worksheet below, where we use this formula to remove the last character from the city names in column B. The formula in D6 looks like this:

=LEFT(B5,LEN(B5)-1)

In the latest version of Excel, we can also use the REGEXREPLACE function to do the same task with a formula like this:

=REGEXREPLACE(B5,".$","")

One way to chop off the last character

[Download the workbook and read the full explanation]

The regex option may look a little scary, but it is just one function :)

In the article above, I explain both approaches in detail. I also show how you can remove the last three characters and how you can make the formula conditional so that it only removes characters when they exist and not when they don't. The REGEXREPLACE approach shines here because regex patterns are so flexible. Click the link above to read the article, download the workbook, and try it yourself.

Note: the classic LEN + LEFT formula will work in any version of Excel. The REGEXREPLACE option requires Excel 365. 

Excel formulas

We maintain a list of over 1000 working formulas here.

If you need more structure, we also offer video training.

Have a great weekend!

Dave

The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem in Excel. You can sign up on our home page.

 

Exceljet Logo
Exceljet
P.O. Box 4804
Salt Lake City, UT 84110

Copyright © 2025 Exceljet, All rights reserved.
You received this email because you are subscribed to our newsletter.
To unsubscribe, click the link below.