Skip to main content

Hi, I have a base where we key in the ID numbers of each person in a column - Males have odd numbers and females have even numbers.


Would like to create a column that automatically tags them as either Male or female based on this data but I cant seem to figure our what formula to use. Could someone help me? It would be of great help.


Examples:


XXXXXX-XX-5557 = Male

XXXXXX-XX-5556 = Female.

Hi Santhanasamy, You can achieve this by comparing the last digit against a Switch() statement, like this:


SWITCH( RIGHT(ID,1), 

"1","Male","2","Female", 

"3","Male","4","Female", 

"5","Male","6","Female", 

"7","Male","8","Female", 

"9","Male","0","Female" 

         )

Yes, that would work fine!


Another way to do this would be to use the MOD() function like this:


IF(MOD(RIGHT(ID,1),2)=0,"Female","Male")


And if the ID field is a number field, you’ll need to convert it to text first in order to make it work with the RIGHT() function like this:


IF(MOD(RIGHT(ID&"",1),2)=0,"Female","Male")

Hi Santhanasamy, You can achieve this by comparing the last digit against a Switch() statement, like this:


SWITCH( RIGHT(ID,1), 

"1","Male","2","Female", 

"3","Male","4","Female", 

"5","Male","6","Female", 

"7","Male","8","Female", 

"9","Male","0","Female" 

         )

Hi, thank you this worked!

Yes, that would work fine!


Another way to do this would be to use the MOD() function like this:


IF(MOD(RIGHT(ID,1),2)=0,"Female","Male")


And if the ID field is a number field, you’ll need to convert it to text first in order to make it work with the RIGHT() function like this:


IF(MOD(RIGHT(ID&"",1),2)=0,"Female","Male")


Hi, I tried the 1st formula but there was a problem, if the ID cell was blank, it would also tag the record.

Hi, I tried the 1st formula but there was a problem, if the ID cell was blank, it would also tag the record.


Then you would need to wrap that formula in another IF statement to check if the ID cell is blank:


If ID is a text field:

IF(ID=BLANK(),"",IF(MOD(RIGHT(ID,1),2)=0,"Female","Male"))


If ID is a number field:

IF(ID=BLANK(),"",IF(MOD(RIGHT(ID&"",1),2)=0,"Female","Male"))

Reply


Terms & Conditions