Sorting

[Ulf Nilsson]

I have a long list of numbers, such as:
106-89-8
141-43-5
197-77-81
50-00-0

When I sort the cells, the 50-00-0 comes either first or
last. I want Excel to sort 50-00-0 first since "50" is
lower than "106". How is this done? The list contains
over 500 entries.

/ Ulf

 

[Domenic]

Hi Ulf,

Assuming that the you want them sorted according to the numbers before
the first hyphen, and your data is in Column A, you can use a helper
column, say Column B, put the following formula, copy down, and sort on
Column B:

=--LEFT(A1,FIND("-",A1)-1)

Hope this helps!

 

[Debra Dalgleish]

Because of the hyphens, the data is treated as text, and the list is
sorted alphabetically, not as a number. Since 5 is greater than 1, all
the items that start with a 1 will sort before the item that starts with
a 5.

You could enter the numbers in three columns, then concatenate the
numbers, and add the hyphens in a fourth column. Then, sort by the
columns that contain the separated numbers. For example

A B C D
1 106 89 8 =A1&"-"&B1&"-"&C1

 

[Domenic]

If you want to sort based on the entire number, you can first separate
them into three different columns using Text to Columns and hyphen as
your delimiter, then sort using Columns A, B, and C, and then put them
back together using something like =A1&"-"&B1&"-"&C1.

Hope this helps!

 

[David McRitchie]

Hi Ulf,
see the following for some further insight.
Sorting TCP/IP Addresses, and the like
http://www.mvps.org/dmcritchie/excel/sorttcp.htm