Forum Discussion
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 ...
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.