Sort by leading digit in Excel

Net75 · 06-28-2009, 05:45 PM

Hi does anyone know how I can sort in excel by leading digit - not by the entire string?

I have tried formatting the list of digits as text, but excel still sorts numerically...

I have a list of 1500 recirds that I need to to sort to look like:

10522
110400
12983
150660
17575

Thanks

Elkar · 06-29-2009, 02:03 PM

You will need to convert your data from numbers to text. Simply chaning the format of the cells to text won't affect existing data. Since it was entered as numbers, Excel will continue to treat it as numbers.

You can retype the data, which will then cause the new format to be applied. But with 1500 records, that isn't really practical. But, there is a little trick you can do with "Text to Columns" to effectively re-enter all of your numbers as text.

- Select your range of numbers
- From the Data Menu, choose "Text To Columns"
- On Step 1, choose "Delimited" then click NEXT
- On Step 2, check "Tab" then click NEXT
- On Step 3, select "Text" for the Column Data Format
- Make sure "Destination" references the same column your data is currently in (it should be this way by default)
- Click FINISH

There you have it. All of your numbers should now be text and should sort as desired.

HTH
Elkar

06-28-2009, 05:45 PM	#1 (permalink)
Net75 Registered User Join Date: Jun 2009 Posts: 1 OS: XP	Sort by leading digit in Excel Hi does anyone know how I can sort in excel by leading digit - not by the entire string? I have tried formatting the list of digits as text, but excel still sorts numerically... I have a list of 1500 recirds that I need to to sort to look like: 10522 110400 12983 150660 17575 Thanks

06-29-2009, 02:03 PM	#2 (permalink)
Elkar Registered User Join Date: Mar 2008 Posts: 69 OS: XP SP2, Vista	Re: Sort by leading digit in Excel You will need to convert your data from numbers to text. Simply chaning the format of the cells to text won't affect existing data. Since it was entered as numbers, Excel will continue to treat it as numbers. You can retype the data, which will then cause the new format to be applied. But with 1500 records, that isn't really practical. But, there is a little trick you can do with "Text to Columns" to effectively re-enter all of your numbers as text. - Select your range of numbers - From the Data Menu, choose "Text To Columns" - On Step 1, choose "Delimited" then click NEXT - On Step 2, check "Tab" then click NEXT - On Step 3, select "Text" for the Column Data Format - Make sure "Destination" references the same column your data is currently in (it should be this way by default) - Click FINISH There you have it. All of your numbers should now be text and should sort as desired. HTH Elkar