Sorting numbers and text together
I have a column which has years in it. The problem is, some of them are entered as a range and some as a single year.
e.g. 2000, 2001-05, 2003-05, 2005, 2010, 2010-12
I am trying to sort this column in such a way that both the ranges and the single years are arranged in order. But, excel sorts the single years (stored as numbers) and the range (stored as text) separately.
How can i arrive at a solution for this issue?
Hi,
Assume your data is in column A then put this formula in column B and copy down as required.
.
=RIGHT(A1,4)
Copy Column B data and Paste / Special / Values over the top. Sort Column A:B with Sort by Column B.
Given 2001-05, 2003-05 will produce 1-05 and 3-05.Originally Posted by abduljaleel.mca
Hi,
Assume your data is in column A then put this formula in column B and copy down as required.
.=RIGHT(A1,4)
Copy Column B data and Paste / Special / Values over the top. Sort Column A:B with Sort by Column B.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Yes, you are right. It should be=LEFT(A1,4)
An alternative, assuming data start in A1:
=TEXT(A1,"###")
in B1 and copy down. Then select all data and use DATA/SORT/sort by column B/Treat text and numbers separately
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
@abduljaleel.mca: Your idea was brilliant!
The formula =Left (A1,4) took the first 4 digits (starting from left) in column A. Hence, I got a column with all single years (the year ranges become single years e.g. 2010-12 became 2010). This way, I was able to sort my column perfectly!
@Glenn Kennedy: Your idea was exactly what I needed! Your formula converted all the years (both the range and single) as text, so that, I was able to sort them without any hitch!
Thank you guys!
You're welcome and thanks for the feedback.
I think I was wrong when I said that your formula just changed the numbers to text. It appears to be doing something more as well!
Numbers can be easily changed to text (Format cells > Text). But, that did not help me with sorting (the single years get sorted first and then the range years).
Can you explain how is your formula different from simply changing the format to text?
The TEXT(A1,"###") buit DOES format everything as text. The key is the sort option - sort numbers and numbers stored as text separately. Excel would prefer to treat number-like strings as numbers. This option forces it to treat everything as text (everything is text, of course), so it returns an alphabetically sorted list.
While I do the sorting, the option "Sort numbers and numbers stored at text separately" does not appear at all (The sort warning box does not appear!). But still, when I use your formula, excel sorts them perfectly, but falters when I use the 'Format cells > Text' method.
I am using Excel 2016.
Last edited by Bradex; 08-03-2016 at 11:49 AM.
I confirmed it by using =istext(a1)
I don't have Excel 2016 so I can't see what it does... But, hey, if it works!!
I think I found the reason!
'Format cells > Text' does not change a number which has been already entered to text. The cell has to be formatted 'before' entering the number. But your formula does change the number to text. That's why it works!
I think that the 'sort warning box' has been removed in Excel 2016.
How is TEXT(A1,"###") different from TEXT(A1,"0")?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks