How can I remove the last 5 characters from a cell...

[lgoldberg]

Is there an excel function to cut x number of characters from the end of a line of text? I want to remove the last 5 characters from each cell (all in column A). These are not blank spaces. The characters (zip codes) are different on every line (there are about 500).

thank you!

 

[PATSYS]

Is there an excel function to cut x number of characters from the end of a line of text? I want to remove the last 5 characters from each cell (all in column A). These are not blank spaces. The characters (zip codes) are different on every line (there are about 500).

thank you!

Assuming the cell is A1:

=LEFT(A1,LEN(A1)-5)

Copy down.

 

[lgoldberg]

Not working...

Thank you for the quick reply. however, this did not work properly. This command is displaying, not *removing*, the text from the cell. Additionally, it is not just taking 5 characters, it is taking almost the entire string.

Anything else I can try?

 

[residnt]

That should have worked. This should only display the left most characters minus the last 5 characters. I have just tried this

=LEFT(C26,(LEN(C26)-5)) and I got the results that PATSYS listed already.

 

[brian.wethington]

the function works wonderfully for me.... I put 123456789 into cell A1 and the formula into B1 and got 1234 in B1, if I then copy and pastespecial values, the actual cell is 1234, not simply displaying. Are the answers it is giving you consistently wrong in a specific way other than displaying not removing?

 

[Scott Huish]

You could also use:
=REPLACE(A1,6,5,"")
which will also work if some of the zip codes don't have the 4 digit extension.

But how did it not work for you?

Both formulas are in this sample:

Book8
	A	B	C	D
1	91256-1234	91256	91256
2	91222-0456	91222	91222
Sheet1

 

[Diane123]

Thanks for posting this, I've used it more times than I can count.