Extract only Even/Odd number from cell

[Karnik]

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

 

[T. Valko]

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?

 

[Karnik]

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



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

 

[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

 

[T. Valko]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	1	2	3	4	5	_	2	4	_	_	_	_	1	3	5	_	_
Sheet1

 

[T. Valko]

What version of Excel are you using?

 

[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
thanks in advance,
Karnik

 

[T. Valko]

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.

 

[Karnik]

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

 

[T. Valko]

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

You're welcome. Thanks for the feedback!