Quick, clean, and to the point

Remove characters from right

Excel formula: Remove characters from right
Generic formula 

To remove the last n characters from a text string, you can use a formula based on the LEFT and LEN functions. You can use a formula like this to strip the last 6 characters of a text string, starting on the left. In the example shown, the formula in E6 is:


which trims " miles" from each value returning just the number. The VALUE function converts text to a numeric value.


The LEFT function is perfect for extracting characters starting from the left side of a text string. We use LEFT in this formula to extract all characters up to the number of characters we want to trim.

The challenge, for values with variable length, is that we don't know exactly how many characters to extract. That's where the LEN function is used.

Working from the inside out, LEN calculates the total length of each value. For D6 (736 miles) the total length is 9. To get the number of characters to extract, we subtract 6, which the length of " miles", including the space character. The result is 3, which is fed to LEFT as the number of characters to extract. LEFT then returns the text "736" as a text value.

Finally, because we want a numeric value (and not text) we run the text through the VALUE function, which converts numbers in text format to actual numbers.

The formula steps look like this:


Note: there is no reason to use the VALUE function if you don't need a numeric result.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.