Welcome Guest ( Log In | Register )

Bump Topic Add Reply RSS Feed

Outline · [ Standard ] · Linear+

> Need help with Excel functions, To find Gender using IC number Q&A

views
     
TSvictorian
post Dec 27 2018, 11:31 AM, updated 7y ago

Look at all my stars!!!
*******
Senior Member
5,637 posts

Joined: Apr 2011
From: Kuala Lumpur
Hi let’s say I have a pool of data and some of the genders are unspecified in an excel sheet. What excel formulas should I use to find the gender using the IC. Something like “if last digit of IC is odd numbers, Gender = Male” and so on. Tenks guys
Flame Haze
post Dec 27 2018, 11:36 AM

Enthusiast
*****
Senior Member
814 posts

Joined: Oct 2009
Use the =ISODD(A1) function, then =IF(A2=TRUE,"Male","Female")

This post has been edited by Flame Haze: Dec 27 2018, 11:38 AM
GPKGB
post Dec 27 2018, 11:38 AM

New Member
*
Newbie
14 posts

Joined: Aug 2010
get last digit =MID(A1, LAST NUMBER, 1)

if statement =IF(MOD(A1,2)=0,"FEMALE","MALE")

This post has been edited by GPKGB: Dec 27 2018, 11:39 AM
SUS2feidei
post Dec 27 2018, 11:39 AM

Ayam tatau, ayam virgin (c)
*******
Senior Member
3,158 posts

Joined: Oct 2013
my way, not very efficient way

1.Copy the list into new column
2.Text to column only the last digit into new cell
3. Use if function in the single digit cell, if odd = male, even = female
ihm11
post Dec 27 2018, 11:43 AM

Getting Started
**
Junior Member
62 posts

Joined: Apr 2018
ic even=female, odd=male is a myth
winter01942
post Dec 27 2018, 11:46 AM

Rating 5/5
*****
Senior Member
857 posts

Joined: Oct 2009
QUOTE(ihm11 @ Dec 27 2018, 11:43 AM)
ic even=female, odd=male is a myth
*
most of the time it works, so

guna je la
cracksys
post Dec 27 2018, 11:47 AM

I'm a Vault Dweller!!
*******
Senior Member
3,668 posts

Joined: Jun 2006
From: Bikini Abyss
the best way is to use INDEX MATCH.
jmas
post Dec 27 2018, 11:47 AM

I can edit title???
*****
Junior Member
830 posts

Joined: Mar 2010
QUOTE(Flame Haze @ Dec 27 2018, 11:36 AM)
Use the =ISODD(A1) function, then =IF(A2=TRUE,"Male","Female")
*
simpler formula

=IF(ISODD(RIGHT(A1,4)),"MALE","FEMALE")

The RIGHT is to capture last 4 digit only.
jmas
post Dec 27 2018, 11:48 AM

I can edit title???
*****
Junior Member
830 posts

Joined: Mar 2010
QUOTE(cracksys @ Dec 27 2018, 11:47 AM)
the best way is to use INDEX MATCH.
*
INDEX MATCH is best way comparing to VLOOKUP
but this is not what he asking for.
Flame Haze
post Dec 27 2018, 11:52 AM

Enthusiast
*****
Senior Member
814 posts

Joined: Oct 2009
QUOTE(jmas @ Dec 27 2018, 11:47 AM)
simpler formula

=IF(ISODD(RIGHT(A1,4)),"MALE","FEMALE")

The RIGHT is to capture last 4 digit only.
*
I learnt something today nod.gif

Excel is god program for audit work
v1n0d
post Dec 27 2018, 11:53 AM

Another roof, another proof.
*******
Senior Member
3,197 posts

Joined: Mar 2007
From: Kuala Lumpur, Malaysia
QUOTE(cracksys @ Dec 27 2018, 11:47 AM)
the best way is to use INDEX MATCH.
*
Thank you for recommending a rocket launcher when all he needed was a revolver.
SUSAmeiN
post Dec 27 2018, 11:54 AM

★★★★★★★
*******
Senior Member
4,735 posts

Joined: Feb 2010
From: ★~Starlight~★
QUOTE(jmas @ Dec 27 2018, 11:47 AM)
simpler formula

=IF(ISODD(RIGHT(A1,4)),"MALE","FEMALE")

The RIGHT is to capture last 4 digit only.
*
thumbup.gif thumbup.gif thumbup.gif
Subcrevv90
post Dec 27 2018, 11:59 AM

Getting Started
**
Junior Member
118 posts

Joined: Mar 2016
=IF(ISEVEN(RIGHT(A2,1)),"FEMALE","MALE")
agent sawyer
post Dec 27 2018, 12:00 PM

New Member
*
Newbie
0 posts

Joined: Oct 2018
QUOTE(victorian @ Dec 27 2018, 11:31 AM)
“if last digit of IC is odd numbers, Gender = Male” and so on. Tenks  guys
*
=IF(ISODD(RIGHT(target cell,1))="True","Male","Female")

You're welcome
cracksys
post Dec 27 2018, 12:17 PM

I'm a Vault Dweller!!
*******
Senior Member
3,668 posts

Joined: Jun 2006
From: Bikini Abyss
QUOTE(v1n0d @ Dec 27 2018, 11:53 AM)
Thank you for recommending a rocket launcher when all he needed was a revolver.
*
helping is never my intention.
pokchik
post Dec 27 2018, 01:36 PM

I'm getting too old for this sh*t
******
Senior Member
1,304 posts

Joined: Mar 2010
QUOTE(PauMaster @ Dec 27 2018, 11:49 AM)
Nur Sajat how?
*
this will pop-up:

Attached Image
Askzra
post Dec 27 2018, 01:39 PM

Getting Started
**
Junior Member
268 posts

Joined: Jul 2012
From: Kuching
QUOTE(jmas @ Dec 27 2018, 11:47 AM)
simpler formula

=IF(ISODD(RIGHT(A1,4)),"MALE","FEMALE")

The RIGHT is to capture last 4 digit only.
*
+1

use this TS.
viex
post Dec 27 2018, 01:44 PM

Getting Started
**
Junior Member
249 posts

Joined: Jan 2017
From: KL
If I, I would use regular expression lagi senang
jmas
post Dec 27 2018, 01:55 PM

I can edit title???
*****
Junior Member
830 posts

Joined: Mar 2010
QUOTE(agent sawyer @ Dec 27 2018, 12:00 PM)
=IF(ISODD(RIGHT(target cell,1))="True","Male","Female")

You're welcome
*
Redundant, IF function itself already checks for TRUE/FALSE.
hirano
post Dec 27 2018, 01:57 PM

凸(`△´＃)
*******
Senior Member
3,336 posts

Joined: Nov 2007
From: Pluto
QUOTE(jmas @ Dec 27 2018, 11:47 AM)
simpler formula

=IF(ISODD(RIGHT(A1,4)),"MALE","FEMALE")

The RIGHT is to capture last 4 digit only.
*
Can ayam ask u if ayam got excel question in future?
Abs0lute
post Dec 27 2018, 01:58 PM

Getting Started
**
Junior Member
50 posts

Joined: Oct 2018
QUOTE(2feidei @ Dec 27 2018, 11:39 AM)
my way, not very efficient way

1.Copy the list into new column
2.Text to column only the last digit into new cell
3. Use if function in the single digit cell, if odd = male, even = female
*
i would be using this if i didnt know the ISODD formula laugh.gif
SUS2feidei
post Dec 27 2018, 02:00 PM

Ayam tatau, ayam virgin (c)
*******
Senior Member
3,158 posts

Joined: Oct 2013
QUOTE(Abs0lute @ Dec 27 2018, 01:58 PM)
i would be using this if i didnt know the ISODD formula laugh.gif
*
tenkiu....i learn something new todei
agent sawyer
post Dec 27 2018, 02:04 PM

New Member
*
Newbie
0 posts

Joined: Oct 2018
QUOTE(jmas @ Dec 27 2018, 01:55 PM)
Redundant, IF function itself already checks for TRUE/FALSE.
*
Ah thanks.
jmas
post Dec 27 2018, 02:07 PM

I can edit title???
*****
Junior Member
830 posts

Joined: Mar 2010
QUOTE(hirano @ Dec 27 2018, 01:57 PM)
Can ayam ask u if ayam got excel question in future?
*
will try to help if possible. thumbup.gif
Blofeld
post Dec 27 2018, 02:12 PM

Look at all my stars!!
*******
Senior Member
4,703 posts

Joined: Mar 2012
QUOTE(jmas @ Dec 27 2018, 11:47 AM)
simpler formula

=IF(ISODD(RIGHT(A1,4)),"MALE","FEMALE")

The RIGHT is to capture last 4 digit only.
*
notworthy.gif
junky_man
post Dec 27 2018, 02:14 PM

The 'bater Debater
******
Senior Member
1,118 posts

Joined: Jul 2008
From: Ur-anus
Formula:

=(Didyouassumemygender)?
spacelion
post Dec 27 2018, 02:16 PM

Ada Liu
******
Senior Member
1,692 posts

Joined: Mar 2009
From: Probation?
QUOTE(v1n0d @ Dec 27 2018, 11:53 AM)
Thank you for recommending a rocket launcher when all he needed was a revolver.
*
TS never ask if he need scalability later on or not

if he want to reuse the function multiple times for other purpose then its better to give rocket launcher

manatau user how stupid or smart
tbcheese
post Dec 27 2018, 02:25 PM

Casual
***
Junior Member
313 posts

Joined: Jul 2009
Why would you even have a stack of IC numbers? lol
v1n0d
post Dec 27 2018, 02:27 PM

Another roof, another proof.
*******
Senior Member
3,197 posts

Joined: Mar 2007
From: Kuala Lumpur, Malaysia
QUOTE(spacelion @ Dec 27 2018, 02:16 PM)
TS never ask if he need scalability later on or not

if he want to reuse the function multiple times for other purpose then its better to give rocket launcher

manatau user how stupid or smart
*
From experience, the latter is more common.
SUSAmeiN
post Dec 27 2018, 02:27 PM

★★★★★★★
*******
Senior Member
4,735 posts

Joined: Feb 2010
From: ★~Starlight~★
QUOTE(hirano @ Dec 27 2018, 01:57 PM)
Can ayam ask u if ayam got excel question in future?
*
bukak ja thread..haha
homicidal85
post Dec 27 2018, 02:41 PM

Getting Started
**
Junior Member
295 posts

Joined: Jun 2006
From: JB
/k programmers all come out dy.
agent sawyer
post Dec 27 2018, 02:43 PM

New Member
*
Newbie
0 posts

Joined: Oct 2018
QUOTE(jmas @ Dec 27 2018, 11:47 AM)
simpler formula

=IF(ISODD(RIGHT(A1,4)),"MALE","FEMALE")

The RIGHT is to capture last 4 digit only.
*
last 1 digit is sufficient
jmas
post Dec 27 2018, 02:44 PM

I can edit title???
*****
Junior Member
830 posts

Joined: Mar 2010
QUOTE(agent sawyer @ Dec 27 2018, 02:43 PM)
last 1 digit is sufficient
*
haha, ya, overkill abit.
Realize afterwards.
rico_n
post Dec 27 2018, 02:56 PM

New Member
*
Junior Member
11 posts

Joined: Jan 2010
many expert here..
tumpang a bit can?

what if i want to copy specific keyword to other column.
let say this keyword always in capital letter

eg: asdad adkjald ABCD asd;ak adskak


i want to copy that ABCD to other colum
Flame Haze
post Dec 27 2018, 02:58 PM

Enthusiast
*****
Senior Member
814 posts

Joined: Oct 2009
QUOTE(rico_n @ Dec 27 2018, 02:56 PM)
many expert here..
tumpang a bit can?

what if i want to copy specific keyword to other column.
let say this keyword always in capital letter

eg: asdad adkjald ABCD asd;ak adskak
i want to copy that ABCD to other colum
*
=MID(target cell, starting position, length of numbers u wanna copy)

In your example, =MID(A1,15,4) coz include spaces

If the position is not fixed, can use this laugh.gif

» Click to show Spoiler - click again to hide... «


Replace the bold part with your target cell

This post has been edited by Flame Haze: Dec 27 2018, 03:32 PM
kiasunkiasi
post Dec 27 2018, 03:04 PM

On my way
****
Senior Member
597 posts

Joined: Nov 2007
QUOTE(Flame Haze @ Dec 27 2018, 11:52 AM)
I learnt something today  nod.gif

Excel is god program for audit work
*
you mean you just learn the RIGHT function? will you be surprised you also learn LEFT function at the same time?
kiasunkiasi
post Dec 27 2018, 03:05 PM

On my way
****
Senior Member
597 posts

Joined: Nov 2007
QUOTE(agent sawyer @ Dec 27 2018, 12:00 PM)
=IF(ISODD(RIGHT(target cell,1))="True","Male","Female")

You're welcome
*
err... that [="True"] part is redundant, no?

edit: okay, this had been answered and settled

This post has been edited by kiasunkiasi: Dec 27 2018, 03:10 PM
Flame Haze
post Dec 27 2018, 03:07 PM

Enthusiast
*****
Senior Member
814 posts

Joined: Oct 2009
QUOTE(kiasunkiasi @ Dec 27 2018, 03:04 PM)
you mean you just learn the RIGHT function? will you be surprised you also learn LEFT function at the same time?
*
Yeah I know how to use RIGHT, LEFT, MID now thanks nod.gif
SUSAud power
post Dec 27 2018, 03:10 PM

Getting Started
**
Junior Member
117 posts

Joined: Nov 2018
QUOTE(victorian @ Dec 27 2018, 11:31 AM)
Hi let’s say I have a pool of data and some of the genders are unspecified in an excel sheet. What excel formulas should I use to find the gender using the IC. Something like “if last digit of IC is odd numbers, Gender = Male” and so on. Tenks  guys
*
YOu split the string, and put the sit string into new column, then you test if that new column is odd or even

Ezpz

Google sheets is better though since it can do real time processing and have more powerful functions

This post has been edited by Aud power: Dec 27 2018, 03:11 PM
rico_n
post Dec 27 2018, 03:25 PM

New Member
*
Junior Member
11 posts

Joined: Jan 2010
my case always position not fixed wan
the formula in the spoiler not werking cry.gif


QUOTE(Flame Haze @ Dec 27 2018, 02:58 PM)
=MID(target cell, starting position, length of numbers u wanna copy)

In your example, =MID(A1,15,4) coz include spaces

If the position is not fixed, can use this laugh.gif

» Click to show Spoiler - click again to hide... «


Replace the bold part with your target cell
*
Flame Haze
post Dec 27 2018, 03:27 PM

Enthusiast
*****
Senior Member
814 posts

Joined: Oct 2009
QUOTE(rico_n @ Dec 27 2018, 03:25 PM)
my case always position not fixed wan
the formula in the spoiler not werking  cry.gif
*
If you copy it straight the "" are a bit wrong sweat.gif Have to manually readjust it

Try this

» Click to show Spoiler - click again to hide... «


This post has been edited by Flame Haze: Dec 27 2018, 03:31 PM
« Next Oldest · The Museum Of Kopitiam · Next Newest »
 

Bump Topic Add ReplyOptions New Topic
 

Change to:
| Lo-Fi Version
 0.0240sec    1.20    6 queries    GZIP Disabled
Time is now: 17th December 2025 - 08:08 PM
All Rights Reserved © 2002- 2025 Vijandren Ramadass (~unite against racism~)
Removal Request
Powered by Invision Power Board © 2025  IPS, Inc.