# Extract only Even/Odd number from cell

#### Karnik

##### Board Regular
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?

I have tried and go it...
=IF(A1/2=INT(A1/2),A1,"")

-Greying hairs do give some grey matters....

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

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

What version of Excel are you using?

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
Karnik

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
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.

Heelo Valko,
It worked , thanks, I appreciate your assistance...
Cheeers
Karnik

Heelo Valko,
It worked , thanks, I appreciate your assistance...
Cheeers
Karnik
You're welcome. Thanks for the feedback!

Replies
10
Views
1K
Replies
2
Views
298
Replies
3
Views
459
Replies
6
Views
537
Replies
7
Views
820

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.

### Which adblocker are you using?

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

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