remove last digit on a series of numbers

s_d_hw

New Member
Joined
Oct 31, 2002
Messages
8
Hi all,

I have a spreadsheet, with about 1,500 lines. In column A is a list of numbers that I need to remove the last digit from each number, for example in A22 is 02602726521 - I need this to be 0260272652. The numbers are all uniqe.

How can I remove the last digit from every number, without going in manually to do so?

Thank you!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
If all are of the same length, try Data|Text to Columns, option Fixed Width.

If not, try...

=LEFT(A1,LEN(A1)-1)
 
Upvote 0
Welcome to the Board!

If your values are all the same length, you can goto Data-->Text To Columns-->Fixed Width and set the delimiter on the last character.

Or you can use this in an adjacent column.

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

Hope that helps,

Smitty

EDIT: Aladin beat me to it! :LOL:
 
Upvote 0
Hi s,

If the data is of varying lengths then try the Left function:
Book1.xls
ABCD
22026027265210260272652
Sheet1



Otherwise, consider using Data / Text To Columns to remove the last digit.

HTH

EDIT: OK, way too slow - back to sleep again I guess :wink:
 
Upvote 0
in VBA

Code:
Sub delete_right_caracter()
For Each ccc In Range(Range("A1"), Range("A65536").End(xlUp))
ccc.Value = Left(ccc, Len(ccc.Value) - 1)
Next ccc
End Sub

bye,
Erik
 
Upvote 0
That's great, thank you so much for your help!!

I can't believe I didn't think to use Text to columns! I guess I was just trying to get it done too fast!

Thanks again!
 
Upvote 0
Just to be thorough, if the cell was actually a number formatted 00000000000, you could use:

=INT(A1/10)

K
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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
Back
Top