Remove characters from right

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. 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 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:

=VALUE(LEFT(D6,LEN(D6)-6))
=VALUE(LEFT(D6,9-6))
=VALUE(LEFT(D6,3))
=VALUE("736")
=736

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