Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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. #1
    New Member
    Join Date
    Oct 2002
    Posts
    8

    Default 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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,333

    Default 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. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,277

    Default 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. #4
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329

    Default 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
    =

    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
    Richie

  5. #5
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default 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
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  6. #6
    New Member
    Join Date
    Oct 2002
    Posts
    8

    Default 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. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677

    Default

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

    =INT(A1/10)

    K

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,333

    Default

    Quote 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. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677

    Default

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

    K

  10. #10
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default Re: remove last digit on a series of numbers

    Aladin,

    Your text to columns witnesses your great experience !

    Erik
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com