ID number to Gender
in [General]
|
Prev: Excel hyperlink within workbook and SharePoint drafts
Next: Are there shortcut keys in Excel for Cell Alignment?
From: Mags on 20 May 2010 04:14 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?
From: Jacob Skaria on 20 May 2010 04:48 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?
From: ck13 on 20 May 2010 04:56 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?
From: Jacob Skaria on 20 May 2010 05:01 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?
From: Ms-Exl-Learner on 20 May 2010 05:10
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,VALUE(MID(TRIM(A1),8,1))<=4),"Female",IF(AND(VALUE(MID(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? |