up vote 0 down vote favorite
share [g+] share [fb]

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?

link|improve this question
67% accept rate
are they numeric or text fields? – aking1012 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
feedback

1 Answer

up vote 2 down vote accepted

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

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

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

link|improve this answer
better than my solution - i was going for numeric files and doing some division and rounding down – aking1012 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
feedback

Your Answer

 
or
required, but never shown
discard

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