ExcelBanter - ID number to Gender

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - ID number to Gender (https://www.excelbanter.com/excel-discussion-misc-queries/264167-id-number-gender.html)

ID number to Gender

I want to see if the employee is a male or female from the ID number?

ID number - 751105 0 931087

The SEVENTH number will tell you if the employee is a male or female
0-4 = Female
5-9 = Male

What formule can I use?

ID number to Gender

Try

=IF(--MID(A1,7,1)<5,"Female","Male")

or to handle errors & blank entries

=IF(AND(N(A1),LEN(A1)6),IF(--MID(A1,7,1)<5,"Female","Male"),"")

--
Jacob (MVP - Excel)

"Mags" wrote:

I want to see if the employee is a male or female from the ID number?

ID number - 751105 0 931087

The SEVENTH number will tell you if the employee is a male or female
0-4 = Female
5-9 = Male

What formule can I use?

ID number to Gender

Hi Jacob,

May i know what is the purpose of putting -- in front of MID and what does
it mean? My formula is quite similar to yours
=IF((MID(A1,7,1)<="4"),"Female","Male") but i need to put "" at the number 4,
though I do not know why I need to do so.

"Jacob Skaria" wrote:

Try

=IF(--MID(A1,7,1)<5,"Female","Male")

or to handle errors & blank entries

=IF(AND(N(A1),LEN(A1)6),IF(--MID(A1,7,1)<5,"Female","Male"),"")

--
Jacob (MVP - Excel)

"Mags" wrote:

I want to see if the employee is a male or female from the ID number?

ID number - 751105 0 931087

The SEVENTH number will tell you if the employee is a male or female
0-4 = Female
5-9 = Male

What formule can I use?

ID number to Gender

The formula I posted compares the MID() result with a numeric as below (<5).
MID() returns a string value and hence I have used -- to convert that to a
numeric
MID(A1,7,1)<5

Your formula compares the MID() result with a string "4" (with double
quotes) and hence there is no need to convert this to a numeric..Instead if
you have used the numeric 4 (without quotes) as below...the formula will not
work correctly...You may try the below on several examples..

=IF((MID(A1,7,1)<=4),"Female","Male")

--
Jacob (MVP - Excel)

"ck13" wrote:

Hi Jacob,

May i know what is the purpose of putting -- in front of MID and what does
it mean? My formula is quite similar to yours
=IF((MID(A1,7,1)<="4"),"Female","Male") but i need to put "" at the number 4,
though I do not know why I need to do so.

"Jacob Skaria" wrote:

Try

=IF(--MID(A1,7,1)<5,"Female","Male")

or to handle errors & blank entries

=IF(AND(N(A1),LEN(A1)6),IF(--MID(A1,7,1)<5,"Female","Male"),"")

--
Jacob (MVP - Excel)

"Mags" wrote:

I want to see if the employee is a male or female from the ID number?

ID number - 751105 0 931087

The SEVENTH number will tell you if the employee is a male or female
0-4 = Female
5-9 = Male

What formule can I use?

ID number to Gender

Assume that you are having the value in A1 cell

A1 Cell
751105 0 931087

Copy and paste the below formula in B1 cell.
=IF(A1="","",IF(AND(VALUE(MID(TRIM(A1),8,1))=0,VA LUE(MID(TRIM(A1),8,1))<=4),"Female",IF(AND(VALUE(M ID(TRIM(A1),8,1))=5,VALUE(MID(TRIM(A1),8,1))<=9), "Male","")))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------

"Mags" wrote:

I want to see if the employee is a male or female from the ID number?

ID number - 751105 0 931087

The SEVENTH number will tell you if the employee is a male or female
0-4 = Female
5-9 = Male

What formule can I use?

ID number to Gender

Thanks it works

"Ms-Exl-Learner" wrote:

Assume that you are having the value in A1 cell

A1 Cell
751105 0 931087

Copy and paste the below formula in B1 cell.
=IF(A1="","",IF(AND(VALUE(MID(TRIM(A1),8,1))=0,VA LUE(MID(TRIM(A1),8,1))<=4),"Female",IF(AND(VALUE(M ID(TRIM(A1),8,1))=5,VALUE(MID(TRIM(A1),8,1))<=9), "Male","")))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------

"Mags" wrote:

I want to see if the employee is a male or female from the ID number?

ID number - 751105 0 931087

The SEVENTH number will tell you if the employee is a male or female
0-4 = Female
5-9 = Male

What formule can I use?

Quote:

Originally Posted by Mags (Post 954871)

assuming there are no spaces in the id number and id number exit in B1 cell,
copy this formulae in C1

=IF(N(RIGHT(LEFT(B1,7),1))<6,"Female","Male")

all the best

ID number to Gender

On Thursday, 20 May 2010 16:14:01 UTC+8, Mags wrote:
I want to see if the employee is a male or female from the ID number?

ID number - 75110501310 8

The TWELVETH number will tell you if the employee is a male or female
EVEN NUMBER = Female
ODD NUMBER = Male

What formule can I use?

ID number to Gender