sorting numbers with an irregular number of digits

  • Thread starter Thread starter hearthd
  • Start date Start date
H

hearthd

I am working on a database and am importing customer numbers that
initially started with 1 digit and now include 5 digit numbers. When
sorted, the numbers with fewer digits are mixed in with the larger
digit numbers. Is there a way to add zeroes to the smaller numbers so
they sort correctly?
 
Assuming your numbers are in column A, starting with A2, enter this
formula in a helper column:

=TEXT(A2,"000000")

and copy down. I've made this 6 digits, but you can leave out one of
the zeros if you wish. You can then include this column in your sort
area, and sort using the helper column as key field. If you don't need
it afterwards, you can delete the helper column.

Hope this helps.

Pete
 
If they are sorting that way then I think they are formatted as text. You
"can" use a helper column as Pete suggest, but you can also convert all your
cells to numbers. To do this simply copy a blank cell, select your range,
paste special selecting "add". This will convert all text that looks like a
number to a number. Your list should then sort how you want. It kind of
depends on your preference really... Hope this helps :)
 
Hope someone can help me with a similar problem. I am putting together a
database relating to horse racing. Each field shows how well a trainer has
done, quoting the number of winners from the total number of runners. An
example entry would be 8-20, which means 8 winners from 20 runners.

However when I try and sort entries to compare it works fine for single
digit numbers on the left, but then stops sorting numerically by the left
hand digit.

Has anyone any ideas?
 
When (if) you get the message from Excel as to how to sort,
choose the option to sort anything that looks like a number as a number.

If you don't get that option or it doesn't work then in an adjoining column
enter this formula... =LEFT(B5,FIND("-",B5,1)-1) + 0
The formula assumes your data starts in cell B5 (adjust as necessary).

Fill the formula down, copy the list of formulas and paste "values" over them.
Sort your data using the new column as the sort column.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Bernie" <[email protected]>
wrote in message
Hope someone can help me with a similar problem. I am putting together a
database relating to horse racing. Each field shows how well a trainer has
done, quoting the number of winners from the total number of runners. An
example entry would be 8-20, which means 8 winners from 20 runners.

However when I try and sort entries to compare it works fine for single
digit numbers on the left, but then stops sorting numerically by the left
hand digit.

Has anyone any ideas?
 
Hope someone can help me with a similar problem. I am putting together a
database relating to horse racing. Each field shows how well a trainer has
done, quoting the number of winners from the total number of runners. An
example entry would be 8-20, which means 8 winners from 20 runners.

However when I try and sort entries to compare it works fine for single
digit numbers on the left, but then stops sorting numerically by the left
hand digit.

Has anyone any ideas?
Click to expand...

First of all, in order to enter values in the form of n-nn, you'll need to
preformat the cells as TEXT, or precede the entry with a single quote (') or
Excel will convert some of those entries to dates.

If you want to sort numerically using native Excel, you'll need to split the
win-total column into two columns, and then either convert them to numeric
values or choose to sort numerically.

There are some free add-ins which can also do similar sorting, but we'd need
more information as to your data layout to be able to go further.
--ron
 
Back
Top