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]

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.
I have ensured that "show formulas" is enabled.

I mean disabled

 

[FormR]

It will not work. I have ensured that "show formulas" is enabled.

Hi, you might to expand on "will not work" - but try formatting the cell you want to place the formula in as general before entering said formula.

Here is one option for the question:

Excel 2012
	A	B
1	5808030162086	Female
2	5808035162086	Male
3	5808033162086	Female
4	5808039162086	Male
Sheet1

Cell Formulas
Range	Formula
B1	=IF(MID(A1,7,1)+0<5,"Female","Male")

 

[seguin85]

=IF(VALUE(MID(A1,7,1))<=4,"Female","Male")

 

[LeilaniMerle]

Hi, you might to expand on "will not work" - but try formatting the cell you want to place the formula in as general before entering said formula.

Here is one option for the question:

Excel 2012

	A	B
5808030162086
5808035162086	Male
5808033162086	Female
5808039162086	Male

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FFFF00"]Female[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=IF(MID(A1,7,1)+0<5,"Female","Male")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi, thanks for your reply.

I have formatted cells correctly. Done everything necessary to show formula. I cut and paste your formula in and press enter. and nothing happens. the formula shows in the cell.

See as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]5808030162086[/TD]
[TD][TABLE="width: 327"]
<tbody>[TR]
[TD="class: xl63, width: 327"]=IF(MID(A1,7,1)+0<5,"Female","Male")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Number cell formatted as Number - Formula formatted as General.

Is there perhaps a problem with my Excel. I have never had this problem before. As a result I cannot check if formula is working or not.

Many thanks for your assistance.

 

[AliGW]

How exactly did you copy and paste? Try clearing the cells and copying again.

 

[Aladin Akyurek]

[..]Is there perhaps a problem with my Excel. I have never had this problem before. As a result I cannot check if formula is working or not.

[...]

Run...

Find and Replace
Find
Set Find What to: =
Replace
Set Replace With to: =
Click Replace All.

 

[mole999]

please confirm your number you are checking is in Row 1, Column A

 

[LeilaniMerle]

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

Run...

Find and Replace
Find
Set Find What to: =
Replace
Set Replace With to: =
Click Replace All.

 

[LeilaniMerle]

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

How exactly did you copy and paste? Try clearing the cells and copying again.