sort by numbers of digits in cells

[mab2819]

Hi,

I need to sort a large document by a certain column. That column consists of
numbers that are six digits and five digits. I need to insert a leading zero
in all those numbers that consist of only five digits. Some of those six
digit numbers already correctly have the leading zero, so when I sort, it
doesn't isolate the five digit ones. Any ideas on how I can do this?
TIA,

 

[David Biddulph]

It sounds as if you may have a mixture of text and numbers. You can check
with ISTEXT() and ISNUMBER(). Solve that problem first (though Excel ought
to have offered you the option to sort text strings which look like numbers
as if they were numbers).

 

[Pete_UK]

Assume your numbers are in column A starting with A2 - insert a new
column B, and put this formula in B2:

=IF(LEN(A2)=5,"0"&A2,""&A2)

then copy down as required. All your values will now be text values of
6 digits, so you can sort using column B as the sort field.

Hope this helps.

Pete

 

[Glenn]

Hi,

I need to sort a large document by a certain column. That column consists of
numbers that are six digits and five digits. I need to insert a leading zero
in all those numbers that consist of only five digits. Some of those six
digit numbers already correctly have the leading zero, so when I sort, it
doesn't isolate the five digit ones. Any ideas on how I can do this?
TIA,

First, convert the entire column to numbers. Place a 1 in an open cell and copy
it. Select your list of data and the Paste Special / Values / Multiply. Then,
use a custom number format of 000000 to display the numbers with leading zeros.

 

[Gord Dibben]

If you have a "number" with a leading zero, it is either a 5 digit number
custom formatted to 000000 or it is text.

Text and numbers sort differently.

First check to see if the 6 digit leading zero numbers are formatted or if
they are text.

If text, in a helper column enter this formula.

Assumes a title in A1 and data from A2 to wherever.

=IF(LEN(A2)=6,A2,"0"&A2)

Copy down.

Sort on column B.


Gord Dibben MS Excel MVP