How to Remove Characters from an Excel String
Suppose you have a column of data in Excel and you want to remove let's say the last 3 chars?
This could be a very time consuming job especially if you have a huge list! But... here's a quick way using 2 functions. The first function is LEFT and the second is LEN.
In the example shown, the formula in E6 is:
Which trims " kms" from each cell.
How it 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 (120 kms) the total length is 7 including the space.