Forum Discussion

PharmDoc's avatar
PharmDoc
Copper Contributor
Jun 02, 2020

Excel - Formatting Column of 11numbers, keep only first 5 (many leading zeros to keep)

I have a column (8800+ rows) that contains a list of numbers that are all 11 characters long, many of which have leading zeros. I want to format the cell to keep only the first 5 digits of each. How can I reformat to accomplish this in only a few steps?

excel

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 02, 2020

    PharmDoc 

    Assuming that you character strings could look, for instance, like (from cell A1 and down):

     

    00000123456

    00001234567

    00123456789

    12345678901

     

    ....the following formula will first create real numbers of them using VALUE(A1), getting rid of the leading zero's, then take the 5 left most digits (LEFT(..........., 5). This creates a text like "12345". Then you can transform it to a number once more by using VALUE(...............)

    =VALUE(LEFT(VALUE(A1),5))

     Copy it down as far as needed. Then you could copy the entire column and paste as values to create a column of 5 digit numbers in stead of a column with formulae. If that is what you want, of course.

Resources