If you want to sort odd and even numbers in Excel, you have come to the right place. Here, we will walk you through 4 easy and effective examples to do the task smoothly.
How to Sort Odd and Even Numbers in Excel: 4 Suitable Examples
In the following dataset, you can see the Name and Code No. column. Here, you can easily notice that Code No. column contains even and odd numbers. Further, using this dataset, we will go through 4 easy examples to sort odd and even numbers in Excel. Here, we used Excel 365. You can use any available Excel version.
1. Using ISODD Function to Sort Odd and Even Numbers in Excel
In this method, we will use the ISODD function to sort odd and even numbers in Excel.
Here, we have added a Helper Column in the dataset where we will use the ISODD function.
Steps:
- In the first place, we will type the following formula in cell D5.
=ISODD(C5)
Formula Breakdown
- ISODD(C5) → The ISODD function returns TRUE when a number is odd, otherwise, the function returns FALSE.
- Output: FALSE
- Explanation: Since cell C5 contains an even number, the ISODD function returns FALSE.
- At this point, press ENTER.
- Therefore, you can see the result in cell D5.
- Furthermore, we will drag down the formula with the Fill Handle tool.
- Hence, you can see the complete Helper Column.
- Now, we will sort the TRUE and FALSE from A to Z. Therefore, Code No. will be sorted as odd and even numbers.
- To do so, we will select the entire Helper Column by selecting cells D5:D14.
- In addition, we will go to the Home tab >> select Editing.
- Furthermore, from Sort & Filter >> select Sort Smallest to Largest.
- Here, you can select Sort Largest to Smallest if you want to sort Z to A.
- At the moment, a Sort Warning will appear.
- Then, make sure Expand the selection is marked.
- After that, click on Sort.
- As a result, you can see the whole dataset has been sorted.
- Hence, you can see the sorted odd and even numbers in Code No. column.
Read More: How to Check If a Number Is Odd in Excel
2. Use of IF, ISODD, and ISEVEN Functions to Sort Odd and Even Numbers in Different Columns
In this method, we will use the combination of the IF and ISODD functions to sort the odd numbers. And, we will use the combination of the IF and ISEVEN functions to sort the even numbers.
Steps:
- First, we will sort the odd numbers.
- Therefore, we will type the following formula in cell D5.
=IF(ISODD(C5),C5," ")
Formula Breakdown
- ISODD(C5) → The ISODD function returns TRUE when a number is odd, otherwise, the function returns FALSE.
- Output: FALSE
- IF(ISODD(C5),C5,” “) → becomes
- IF(FALSE,C5,” “)
- Output: Blank Cell
- Explanation: Since cell C5 contains an even number, the ISODD function returns FALSE, and the IF function returns a blank cell.
- IF(FALSE,C5,” “)
- At this point, press ENTER.
- Therefore, you can see the output in cell D5.
- Moreover, we will drag down the formula with the Fill Handle tool.
- Therefore, you can see the sorted odd numbers in the Odd Numbers column.
- Further, we will sort the even numbers.
- To do so, we will type the following formula in cell E5.
=IF(ISEVEN(C5),C5," ")
Formula Breakdown
- ISEVEN(C5) → The ISEVEN function returns TRUE when a number is even, otherwise, the function returns FALSE.
- Output: TRUE
- IF(ISEVEN(C5),C5,” “) → becomes
- IF(TRUE,C5,” “)
- Output: 1234
- Explanation: Since cell C5 contains an even number, the ISEVEN function returns TRUE, and the IF function returns 1234.
- IF(TRUE,C5,” “)
- After that, press ENTER.
- Therefore, you can see the result in cell E5.
- Furthermore, we will drag down the formula with the Fill Handle tool.
- Therefore, you can see the sorted even numbers in the Even Numbers column.
Read More: How to Verify Odd and Even Using IF Function in Excel
3. Sorting Odd and Even Numbers in an Ascending Order
In this method, we will use the combination of IFERROR, IF, ROW, INDEX, MOD, and SMALL functions to sort odd and even numbers in Excel. This method is helpful when you want the odd or even numbers to be sorted in ascending order.
Steps:
- In the beginning, we will type the following formula in cell D5.
=IFERROR(SMALL(IFERROR(INDEX($C$5:$C$14,SMALL(IF(MOD($C$5:$C$14,2)=1,ROW($C$1:$C$10)),ROW($C$1:$C$10))),""),ROW(C1)),"")
Formula Breakdown
- MOD($C$5:$C$14,2) → the MOD function divides a number with a divisor and returns a reminder.
- Output: {0;0;1;0;1;0;1;1;0;0}
- MOD($C$5:$C$14,2)=1 → becomes
- {FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE})
- ROW($C$1:$C$10) the ROW function → returns the row number of a range of cells.
- Output: {1;2;3;4;5;6;7;8;9;10}
- SMALL(IF(MOD($C$5:$C$14,2)=1,ROW($C$1:$C$10)),ROW($C$1:$C$10))),””) → becomes
- SMALL(IF{FALSE;FALSE;3;FALASE;5;FALSE;7;8;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10}))
- Output: {3;5;7;8;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!})
- SMALL(IF{FALSE;FALSE;3;FALASE;5;FALSE;7;8;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10}))
- INDEX($C$5:$C$14,SMALL(IF(MOD($C$5:$C$14,2)=1,ROW($C$1:$C$10)),ROW($C$1:$C$10))),””) → becomes
- INDEX($C$5:$C$14{3;5;7;8;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!})
- Output:{2367;7891;9981;8923;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}
- INDEX($C$5:$C$14{3;5;7;8;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!})
- IFERROR(INDEX($C$5:$C$14,SMALL(IF(MOD($C$5:$C$14,2)=1,ROW($C$1:$C$10)),ROW($C$1:$C$10))),”” → becomes
- IFERROR({2367;7891;9981;8923;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!})
- Output: {2367;7891;9981;8923;” ”;” ”;” ”!;” ”;” ”;” ”}
- IFERROR({2367;7891;9981;8923;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!})
- ROW(C1) → becomes
- Output: 1
- SMALL(IFERROR(INDEX($C$5:$C$14,SMALL(IF(MOD($C$5:$C$14,2)=1,ROW($C$1:$C$10)),ROW($C$1:$C$10))),””),ROW(C1)),””) → becomes
- SMALL({2367;7891;9981;8923;” ”;” ”;” ”!;” ”;” ”;” ”},1)
- Output: 2367,” ”
- SMALL({2367;7891;9981;8923;” ”;” ”;” ”!;” ”;” ”;” ”},1)
- IFERROR(SMALL(IFERROR(INDEX($C$5:$C$14,SMALL(IF(MOD($C$5:$C$14,2)=1,ROW($C$1:$C$10)),ROW($C$1:$C$10))),””),ROW(C1)),””) → becomes
- IFERROR(2367,” ”)
- Output: 2367
- IFERROR(2367,” ”)
- In addition, press ENTER.
- Therefore, you can see the result in cell D5.
- Furthermore, we will drag down the formula with the Fill Handle tool.
- Hence, you can see the complete Odd Numbers column.
- Afterward, to sort the even number, we will type the following formula in cell E5.
=IFERROR(SMALL(IFERROR(INDEX($C$5:$C$14,SMALL(IF(MOD($C$5:$C$14,2)=0,ROW($C$1:$C$10)),ROW($C$1:$C$10))),""),ROW(C1)),"")
- Here, the formula has only one difference from the previous one.
- We take MOD($C$5:$C$14,2)=0 because we want to get the even numbers, and therefore, we want the reminder to become 0.
- At this point, press ENTER.
- As a result, you can see the output in cell E5.
- Furthermore, we will drag down the formula with the Fill Handle tool.
- As a result, you can see the complete Even Numbers column.
Read More: How to Fill Odd Numbers in Excel
4. Inserting ISODD and MID Functions to Sort Odd and Even Numbers Between Texts
In the following dataset, you can see Code No. column contains both text and numbers. Here, we want to sort the even and odd numbers based on the middle number of the text. We will use the ISODD and MID functions for this case.
Steps:
- First, we need a Helper Column to perform our calculation.
- Then, we will type the following formula in cell C5.
=ISODD(MID(C5,4,2))
Formula Breakdown
- MID(C5,4,2) becomes
- MID(“Y1-02-4Y”,4,2)
- Output: 02
- ISODD(MID(C5,4,2)) becomes
- ISODD(02)
- Output: FALSE
- ISODD(02)
- MID(“Y1-02-4Y”,4,2)
- Then, press ENTER.
- Hence, you can see the result in cell D5.
- Moreover, we will drag down the formula with the Fill Handle tool.
- Hence, you can see the complete Helper Column.
- Now, we will sort the TRUE and FALSE from A to Z. Therefore, Code No. will be sorted as odd and even numbers.
- To do so, we will select the entire Helper Column by selecting cells D5:D14.
- In addition, we will go to the Home tab >> select Editing.
- Furthermore, from Sort & Filter >> select Sort Smallest to Largest.
- Here, you can select Sort Largest to Smallest if you want to sort Z to A.
- At the moment, a Sort Warning will appear.
- Then, make sure Expand the selection is marked.
- After that, click on Sort.
- Therefore, you can see the sorted Code No. column, where the middle number of the text strings has been sorted according to the even and odd numbers.
Practice Section
You can download the above Excel file and practice the explained examples.
Download Practice Workbook
You can download the Excel file and practice while reading this article.
Conclusion
Here, we show you 4 easy examples to sort odd and even numbers in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.