Jun 01 2020 08:12 PM
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?
Jun 01 2020 09:36 PM
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.