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?

  • 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

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'.

  • 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
  • 1
    @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
  • 1
    For OpenOffice, just replace the comma with a semicolon: =LEFT(A1;LEN(A1)-6) – Linker3000 Nov 5 '10 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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