Extract Gender from I.D. Number

[LeilaniMerle]

Hi all, There has been a post previous to this regarding Extracting I.D. But I cannot seem to access it. Also that Solution has not worked for me.

1) I need to extract the gender (Male, or Female) from I.D. Numbers.

If my I.D is 580803 0162 086
The 7th Number tells the gender.

If that number is anything from 0-4 it is Female
If that number is anything from 5-9 it is Male

I am typing the number directly into a cell with no "I.D." IN front of it. e.g. (5808030162086) then i am formatting cell to number then getting rid of the decimals.

I am using Excel 2010 from Microsoft Office Professional 2010.

2) I seem to be having a problem when copying a formula to a cell, i.e. say from the internet. It will not work. I have ensured that "show formulas" is enabled.

Hope someone can assist me on these issues.

 

[LeilaniMerle]

Also have actually typed it out and still nothing.

I highlighted from this forum, Ctrl C, went to cell B1 and Ctrl V?
Is this not right?

 

[AliGW]

Did you click inside the cell first? I have seen something similar in Excel 2016. Make sure that it hasn't pasted with a space before the = sign.

 

[FormR]

I did that thanks you Aladin, and got an error sayin the formula contains an error.

What happens if you manually enter the formula into a cell that you have previously formatted as general?

 

[mole999]

apart from the cell being set as text which would leave the = sign showing

are you using ; as your separator and not , (language dependant)

 

[Aladin Akyurek]

I did that thanks you Aladin, and got an error sayin the formula contains an error.

You mean you got the formula to run but it says the formula contains an error, right?

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

If you are on a non-American system:

=IF(MID(A1;7;1)+0 < 5;"Female";"Male")

 

[LeilaniMerle]

No space...sigh

Did you click inside the cell first? I have seen something similar in Excel 2016. Make sure that it hasn't pasted with a space before the = sign.

 

[AliGW]

Can you successfully copy and paste the formula into a new sheet? I can copy it fine here.

 

[LeilaniMerle]

Aladin,
I ran the find and replace the = and it says it contained an error.

Now you have identified in the non-American system and that formula works like a dream.

Thank you so very very much. Please could you, for arguments sake just put here, why there is a difference in systems? Is it the keyboards or the Office Installation Language? Please help me understand.

e.g. when I used the comma to seperate the digit for the MID it would not see it, but when used the ; it saw it. I am in South Africa.

Once again, Many many thanks for all your help everyone.

Merle♪♫♪ Compliments of the season to the world ♪♫♪

You mean you got the formula to run but it says the formula contains an error, right?

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

If you are on a non-American system:

=IF(MID(A1;7;1)+0 < 5;"Female";"Male")

 

[AliGW]

You mean you got the formula to run but it says the formula contains an error, right?

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

If you are on a non-American system:

=IF(MID(A1;7;1)+0 < 5;"Female";"Male")

Just as a point of information, in case it matters here, the UK locale uses the comma instead of the semi-colon, too, in fact I believe all English-speaking locales do the same.

 

[LeilaniMerle]

Well thats what I mean. I installed MS Office using the British English, and so is it that that makes the difference? cause it only saw the semi-colon, not the comma.

Just as a point of information, in case it matters here, the UK locale uses the comma instead of the semi-colon, too, in fact I believe all English-speaking locales do the same.