How do you delete the last character in a cell for multiple cells in Excel?

Question

Ok, I need to delete the last character in this column for all of the values. there are over 1600 lines so it would be a waste to do it manually. I don't know how to write a script that would go and delete the last letter in each cell, can someone help me?

ex. the column looks like:

aa1234567

ab6543217

bb2034897

... etc.

I just need to delete the last character in each cell in the entire column. thanks in advance!

Answer 1

IF what you want to end up with is all the same length, there's a couple of ways, but if the end result is variable length then I think you're out of luck.

Let's say the data right now is 8 characters, and you want 7.

Highlight the column with the data.  Go to Data, Text to Columns.  Choose Fixed Width, Next.  Move the slider back one character.  Then next along and finish.

Or, you could insert a blank column next to it.  In the b1 cell (for instance), put the formula =left(a1,7)

Then copy the formula down the column.

Answer 2

assuming all the entries are NOT the same length (as the previous answer is assuming), simply do

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

this will copy everything in A1 starting from the left, minus 1 character (the last one).

then just copy/paste this formula down as far as you need to.

once that's done you can copy all the formulas, then do a "paste special" and select the "values" option, and paste it back over your original data and delete all the formulas.  The paste "values" basically pastes the results of the formula, not the formula.

Answer 3

Are all the entries the same length?

If so you are in Luck!

Amusing all your entries are in Column A.

in Column B (or whatever column) type:

=LEFT(A1,8)

And then drag it down.

--

This will Give you a a New column with the values you want.

You can then Copy and paste the VALUES over top.