Exceljet

Quick, clean, and to the point

Remove characters from right

Excel formula: Remove characters from right
Generic formula 
=LEFT(text,LEN(text)-n)
Explanation 

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

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

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