damienjohnson12
New Member
- Joined
- Jan 4, 2018
- Messages
- 3
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.
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.