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,".$","")
[
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.