Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way in microsoft excel or open office calc to remove the last n chars from a field.

Lets say all fields in one column are 31 chars long and I want to remove the last 6 chars.

Is there an easy way with excel or calc to do this?

share|improve this question
are they numeric or text fields? – RobotHumans Nov 5 '10 at 13:54
This is a text field. And I could write a simple script to do it but would rather use an application to click a few buttons which is why I asked for this type of solution. – Chris Nov 5 '10 at 13:59
up vote 3 down vote accepted

If your data is in A1, you could put the following formula in B1 (or elsewhere):


If you want a fixed result rather than a calculated one, you could then copy the result and use 'paste as values'.

share|improve this answer
better than my solution - i was going for numeric files and doing some division and rounding down – RobotHumans Nov 5 '10 at 15:00
Is this excel? Doesn't work in open office calc. – Chris Nov 5 '10 at 15:10
Did work in excel, how would I apply that to all rows though? I have the formula as "=LEFT($K2,LEN($K2)-6)" which worked for one cell but I want to apply this to 100k records. I know i can drag down the column but thats a lot of dragging for 100k rows – Chris Nov 5 '10 at 15:17
@Chris, select a cell in a column that has values (K would work in your case), press Ctrl+Down arrow. You should be at the bottom of your data. Select the bottom cell in the column you want to fill with the formula, press Shift+Ctrl+Up arrow, then press Ctrl+D. – Martha Nov 5 '10 at 18:51
For OpenOffice, just replace the comma with a semicolon: =LEFT(A1;LEN(A1)-6) – Linker3000 Nov 5 '10 at 19:11

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.