Excel Text functions in combination with an IF function

[damienjohnson12]

Hi all, I am a new member to the forum. Need to know how to use IF and MID function together, in order to return a text value:

Examples of scenarios below:

1.Gender: Display M (Male) and F (Female). Extract it from a 13-digit ID number( e.g. 6105245137079). The gender is determined by the "7th" digit of the ID number:Females are indicated by 0-4 and Males indicated by 5-9.

My attempt, as follows: =IF(MID(F2,7,1)=>4,"F",IF(MID(F2,7,1)=>9,"M")))) This however does not return the "M" when copied down.

2. Birth Month: Display the name of the month in which the person was born. Extract from e.g. 1957-11-26. Thus 01 is January, 02 is February etc. It requires text values for up to 12 months.

My attempt, as follows: =IF(MID(E2,6,2)="01","January",IF(MID(E2,6,2)='02","February"...it works, however after the 7th month it gives an error: too many arguments.

3. South African citizen: Display values Yes or No. Extract from the "11th" digit of the ID number (e.g. 7105245312158). Thus 0 indicates: South African and 1 indicates a foreigner.

My attempt, as follows: IF(MID(D2,11,1)="1",Yes",IF(MID(D2,11,1)="0","No")))) This however, returns a FALSE value.

Hope the above is clearly stated. Thank you in advance for your assistance in this regard.

 

[Eric W]

Welcome to the board.

Try:

	D	E	F
2	7105245312158	1957-11-26	6105249137079
3	Yes	November	M

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D3 =IF(MID(D2,11,1)="1","Yes","No") E3 =TEXT(E2,"MMMM") F3 =IF(MID(F2,7,1)<="4","F","M") <tbody> </tbody>

<tbody>
</tbody>

 

[damienjohnson12]

Apologies:Correction to the above under scenario 1 Gender; my attempt, =IF(MID(F2,7,1)=<4,"F",IF(MID(F2,7,1)=<9,"M"))))

 

[damienjohnson12]

Hi Eric W

Many thanks for the your assistance. It works perfectly well now.

Much appreciated.

 

[Eric W]

You're welcome.