Excel Text functions in combination with an IF function

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.:confused:
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Welcome to the board.

Try:

DEF
71052453121581957-11-266105249137079
YesNovemberM

<tbody>
[TD="align: center"]2[/TD]

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

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=IF(MID(D2,11,1)="1","Yes","No")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=TEXT(E2,"MMMM")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=IF(MID(F2,7,1)<="4","F","M")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top