Sort the numbers in ascending order. Assuming you have a header row so
that the numbers start in A2, put this in B2:

=LEFT(A2,5)

and copy down, then put this in C2:

=IF(LEFT(A2,5)=LEFT(A1,5),"",COUNTIF(B:B,B2))

and copy down. This should give you the totals for each leading 5-
digits, so you can scan through looking for 10 (or 11, as you might
also have just the 5-digit part) and then manually replace the block
of 10 with a single row.

You can delete columns B and C when you've finished.

Hope this helps.

Pete

On Sep 29, 6:28*am, "Chris Mitchell"
wrote:
I have a list of numbers, some are 5 digits the others are 6 digits long.

For some of the 6 digit numbers there is a complete set, i.e. XXXXX0 through
to XXXXX9. *Where this is the case I want to replace the existing 10 entries
with a single 5 digit number i.e. XXXXX. *Where there is an incomplete set
of 6 digit numbers, then I want to leave the existing 6 digit numbers in the
list.

I also want all existing 5 digit numbers to be included in the finished
list.

How can I get excel to do this?

TIA.

Chris.