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

lgoldberg

New Member
Joined
Jul 23, 2007
Messages
4
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!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
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

New Member
Joined
Jul 23, 2007
Messages
4
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

Board Regular
Joined
Nov 19, 2002
Messages
168
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCD
191256-12349125691256
291222-04569122291222
Sheet1
 

Forum statistics

Threads
1,181,724
Messages
5,931,688
Members
436,798
Latest member
spprtpplcm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top