Extract only Even/Odd number from cell

Karnik

Board Regular
Joined
Aug 1, 2011
Messages
58
Hello Experts,
Wondering if I could fish out only even and odd numbers form a cell as follows, to sort out . All numbers are whole without any decimals.

A1 B1 C1 D1
89 64 100 5

Results will look like:

E1 F1 G1 H1 I1
64 100 (blank) 89 5

Ideally without VB!
Thanks
Karnik
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hello Experts,
Wondering if I could fish out only even and odd numbers form a cell as follows, to sort out . All numbers are whole without any decimals.

A1 B1 C1 D1
89 64 100 5

Results will look like:

E1 F1 G1 H1 I1
64 100 (blank) 89 5

Ideally without VB!
Thanks
Karnik
Will it always be 4 cells per row?
 
Upvote 0
I have tried and go it...
=IF(A1/2=INT(A1/2),A1,"")



-Greying hairs do give some grey matters....
 
Upvote 0
Hi Valko
Infact there are 5 cells per row.... my solution is still primitive as it does involve moving out columns by using cut/paste.
Do you have any better suggestion?
Thanks
Karnik
 
Upvote 0
Hi Valko
Infact there are 5 cells per row.... my solution is still primitive as it does involve moving out columns by using cut/paste.
Do you have any better suggestion?
Thanks
Karnik
If there are 5 cells then I assume that means all 5 cells could be even or all 5 cells could be odd.

What I would do is use up to 5 cells to extract the even numbers then another 5 cells to extract the odd numbers. You can still separate the groups of cells by an empty cell. Like this:

Book1
ABCDEFGHIJKLMNOPQ
112345_24____135__
Sheet1
 
Upvote 0
Hi Valko,
You are spot on! yes this is exactly what I am doing now as shown in Sheet 1 above.
I use Excel 2007
thanks in advance,
Karnik
 
Upvote 0
Hi Valko,
You are spot on! yes this is exactly what I am doing now as shown in Sheet 1 above.
I use Excel 2007
thanks in advance,
Karnik
With the numbers in the range A1:E1...

For the even numbers...

Enter this array formula** in G1 and copy across to K1:

=IFERROR(INDEX(1:1,SMALL(IF(ISNUMBER($A1:$E1),IF(MOD($A1:$E1,2)=0,COLUMN($A1:$E1))),COLUMNS($G1:G1))),"")

For the odd numbers...

Enter this array formula** in M1 and copy across to Q1:

=IFERROR(INDEX(1:1,SMALL(IF(ISNUMBER($A1:$E1),IF(MOD($A1:$E1,2)>0,COLUMN($A1:$E1))),COLUMNS($M1:M1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0

Forum statistics

Threads
1,211,772
Messages
6,103,876
Members
447,882
Latest member
LORENA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top