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

Occasional Visitor

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


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







....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(...............)


 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.