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

=if(mid(a1,8,4)*1<5000,"Female","Male"))


"Catherine" wrote in message
...
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

Look in HELP index for
1.MID
2. IF
combine

--
Don Guillett
SalesAid Software

"Catherine" wrote in message
...
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

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


"Catherine" wrote:

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

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.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Catherine" wrote in message
...
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

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

"Bernard Liengme" wrote:

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.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Catherine" wrote in message
...
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

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")

"Bernard Liengme" wrote:

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.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Catherine" wrote in message
...
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

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



"Catherine" wrote:

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