How do I identify the 7th digit in a 13 digit number, then establi

C

Catherine

An SA identity number has 13 digits, the 1st 6 digits are your birthday, the
next 4 digits indicate male or female, I don't know what the last three
indicate. Anyway I wan't to write a formula that will tell me if employees
are male or female based on their ID number. e.g. ID 730825 0137 088 is a
female because the middle four digits are below 5000, ID 730825 5432 087 is a
male because the middle four digits are above 5000. I do realize that "text
to columns" and a simple if function would suffice, but it is important that
the ID number remains in one column. Please help, it would be much
appreciated!
Catherine
 
L

LanceB

=--MID(A1,7,1) if you just want the 7th digit
=--MID(A1,7,4) if you just want all 4t
 
B

Bernard Liengme

Is the id entered as 730825 0137 088 or as ID 730825 0137 088
If the first use: =IF(--MID(A1,8,1)<5,"M","F")
if the second use =IF(--MID(A1,11,1)<5,"M","F")
The double negation converts the text digit to a numeric value.
 
K

Kassie

Change the "<" to read ">", or else swap the "M" and "F", as <5000 = Female,
and >5000 = Male
 
K

Kassie

Sorry, but you will then also have to change it to >=, because 5 and up
equals male

It should therefore read:

If the first use: =IF(--MID(A1,8,1)<5,"F","M")
if the second use =IF(--MID(A1,11,1)<5,"F","M")
 
C

CLR

Conditional formatting on your ID column.......

Condition 1, Formula is: =--MID($A1,8,1)<5, format cell background as pink
Condition 2, Formula is: =--MID($A1,8,1)>=5, format cell background as blue

Vaya con Dios,
Chuck, CABGx3
 
Top