Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
MS Excel sorting question?
I have a very long spreadsheet, thousands of rows. The cells I want to sort by are formatted as text. I have tried formatted as number and it still does not work. For example, I have something like this, three separate rows:
3-45
6-3
4-9
I want it to look like
3-45
4-9
6-3
but Excel will not do this, whether formatted as number or text. The hyphen has to be in there b/c it is a land tax parcel number.
it's excel 2010 on windows 7. i was just giving an example there. i have thousands of lines in the spreadsheet.
also i get the "sort warning" "The following sort key may not sort as expected b/c it contains some numbers formatted as text: Tax Parcel # What would you like to do? --Sort anything that looks like a number, as a number" OR "Sort number and numbers stored as text separately." Neither way sorts it correctly.
3 Answers
- sewrobbLv 710 years agoFavorite Answer
Cracked it!
I've got Excel from Office 2000 and went and had a play with mine with your numbers.
However first I formatted the column for text. A1, A2, and A3
Then entered the numbers as you wrote them.
Then I put the cursor on the top one so the cell A1 was highlighted and than all I did was use the AZ Sort Ascending button ↓ and it put them in the order you wanted.
I then expanded the column to A10 and chucked in a load of random ones in as well and it sorted them all in the right order.
Source(s): Experienced1 - puakaLv 510 years ago
The whole column must be sorted as text in order to sort 'correctly'. Sorting as text creates other issues as 1, 12, 123, 2, 21, 213 sorted in the same sequence. The fool proof way to create a text column is to use the formula ="'" & A1. That is an apostrophe enclosed by quotes concatenated to the cell in the adjacent column. Check the results. It should look the same as the apostrophe is a hidden character. If the results appear wierd then you have a problem there as they could have been formatted as dates. 6-3 could be June 3rd. There are ways around it though.
- braydenLv 710 years ago
What version of excel is this? And are you sure it's formatted as text. I just put your sample in and sorted and it sorted it perfectly.
Either your cells are not properly formatted as text, you're not using the sort feature correctly, or there is some sort of bug in your excel software. If the latter you need to download the latest updates from Microsoft.
