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

%3CLINGO-SUB%20id%3D%22lingo-sub-1432495%22%20slang%3D%22en-US%22%3EExcel%20-%20Formatting%20Column%20of%2011numbers%2C%20keep%20only%20first%205%20(many%20leading%20zeros%20to%20keep)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1432495%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20(8800%2B%20rows)%20that%20contains%20a%20list%20of%20numbers%20that%20are%20all%2011%20characters%20long%2C%20many%20of%20which%20have%20leading%20zeros.%20I%20want%20to%20format%20the%20cell%20to%20keep%20only%20the%20first%205%20digits%20of%20each.%20How%20can%20I%20reformat%20to%20accomplish%20this%20in%20only%20a%20few%20steps%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1432495%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
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
Highlighted

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