# remove last digit on a series of numbers

This is a discussion on remove last digit on a series of numbers within the Excel Questions forums, part of the Question Forums category; Hi all, I have a spreadsheet, with about 1,500 lines. In column A is a list of numbers that I ...

1. ## remove last digit on a series of numbers

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!

2. ## Re: remove last digit on a series of numbers

If all are of the same length, try Data|Text to Columns, option Fixed Width.

If not, try...

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

3. ## Re: remove last digit on a series of numbers

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!

4. ## Re: remove last digit on a series of numbers

Hi s,

If the data is of varying lengths then try the Left function:

******** ******************** ************************************************************************>
 Microsoft Excel - Book1.xls ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B22 =

A
B
C
D
22
026027265210260272652**
 Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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

HTH

EDIT: OK, way too slow - back to sleep again I guess

5. ## Re: remove last digit on a series of numbers

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

6. ## Re: remove last digit on a series of numbers

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!

7. Just to be thorough, if the cell was actually a number formatted 00000000000, you could use:

=INT(A1/10)

K

8. Originally Posted by kkknie
Just to be thorough, if the cell was actually a number formatted 00000000000, you could use:

=INT(A1/10)

K
You'd loose the leading zeroes.

9. Assumed you would format the new cell the same way as the original one...

K

10. ## Re: remove last digit on a series of numbers

Your text to columns witnesses your great experience !

Erik

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•