Excel formula: Remove characters from right

Generic formula

=LEFT(text,LEN(text)-n)

Summary

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.

Explanation

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.

Author

Dave Bruns

Related formulas

Remove first character

This formula uses the REPLACE function to replace the first character in a cell with an empty string (""). The arguments for REPLACE are configured as follows: old_text is the original value from column B start_num is hardcoded as the number 1...

Remove last word

This formula uses the MID function to remove the last word from a text string. The main challenge is to figure out where the last word begins. The formula is a bit convoluted, but the steps are simple. We first count how many spaces exist in the...

Remove file extension from filename

The core of this formula is the LEFT function which simply extracts text from the file name, starting at the left, and ending at the character before the first period ("."). = LEFT ( filename , characters ) The FIND function is used to figure out...

Split text and numbers

In the first formula above, we are using the FIND function to locate the starting position of the number. For the find_text, we are using the array constant {0,1,2,3,4,5,6,7,8,9}, this causes the FIND function to perform a separate search for each...

Split numbers from units of measure

Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested only, so take care if you use or adapt. Sometimes you encounter data that mixes units directly with numbers (i.e...

Related functions

Excel LEFT Function

The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT("apple",3) returns "app".

Excel LEN Function

The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.

Excel VALUE Function

The Excel VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. Normally, the VALUE function is not needed in Excel, because Excel automatically converts text to numeric...

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.