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? 

120 kms
12 kms
15 kms
202 kms
1000 kms

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:

=LEFT(D6,LEN(D6)-3)

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.

This gives the result 120 but you'll notice the number is stored as text. If you want it to be stored as a number, simply wrap the VALUE()( function around the whole thing to give:-

=VALUE(LEFT(D6,LEN(D6)-6))

See you again soon, please comment and share if you found useful!
Anita Lund
www.trainers-direct.com.au  Sydney
www.stormtraining.com UK