Remove last characters from right in a cell in Excel
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 3 characters, last 5 characters of a value, starting on the left.
Formula
=LEFT(text,LEN(text)-n)
Note: there is no reason to use the VALUE function if you don’t need a numeric result.
Explanation
In the example shown, the formula in E6 is:
=VALUE(LEFT(D6,LEN(D6)-6))
Which trims ” miles” from each value returning just the number.
How this formula works
The LEFT function is perfect for extracting characters from the left side of a value. 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 (not text) we run the text through the VALUE function, which converts numbers in text format to plain numbers.
The formula evaluation goes like this:
=VALUE(LEFT(D6,LEN(D6)-6))
=VALUE(LEFT(D6,9-6))
=VALUE(LEFT(D6,3))
=VALUE("736")
=736