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

Copper Contributor

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?

1 Reply

@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.