Contents
A classic task that almost every Microsoft Excel user faces from time to time is to determine the gender of each person on the list. Let’s look at a few solutions for such a task.
Option 1. Full name, only “ours”
Let’s start, to warm up, with the simplest case, when we have correctly written full names for all the people on the list. As it is easy to figure out, for most residents of the former CIS, a simple principle will work here: “if the middle name ends in H, then this is a man, otherwise it is a woman.” You can implement this logic with a simple formula:
Function RIGHT (RIGHT) extracts one character from the right from the full name (the last letter of the patronymic), and the function IF (IF) checks the extracted character and outputs “w” or “m”, depending on the result of the check.
Option 2. Full names, there are “expats”
If the list contains names not only of the type (let’s call them “expats”), then one more check can be added to the above formula to catch them too:
That is, “m” will be displayed only if the patronymic ends with H, “g” – if it ends with A. In all other cases (“expats”), our formula will produce three question marks.
Option 3. Incomplete or rearranged full names, only “ours”
If not everyone has a patronymic in our list (or they don’t exist at all) or the full name goes in a different order (IFO, IF, FI), then we will have to use a fundamentally different approach. Let’s create a lookup table with all female names (I used Wikipedia for this):
I converted the created table into a “smart” one (select it and click Ctrl + T), so that later you don’t have to think about its size and supplement the directory with new names at any time. On the tab that appears Constructor (Design) it is better to give a smart table a separate name (for example Wives) to use it later in formulas:
The formula we need to determine gender will look like this:
Let’s break it down step by step using the first person as an example:
Function SEARCH (SEARCH) searches for occurrences in turn of each female name from the smart table Wives into the line “Khrabrova Alla Sergeevna” and outputs either the #VALUE error (if not found), or the serial number of the character, starting from which the name is included in the full name. At the output we get an array:
{#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:10:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:
#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN !:
#SO!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN !:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:
#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#SO!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN!:#MEAN !:
#SO!:#SO!:#SO!:#SO!:#SO!}
The number 10 in the seventh position in this array actually means that the seventh female name Alla from a smart reference table is included in the first full name Khrabrova Alla Sergeevna starting from character 10.
Then the function IFERROR (IFERROR) replaces errors #VALUE! to zero. As a result, we get:
{0:0:0:0:0:0:10:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0}
Function SUM (SUM) sums all the numbers in the resulting array, and if the result is a number greater than zero, then the function IF (IF) outputs ‘g’, otherwise ‘m’.
Do not forget to press the keyboard shortcut after entering the formula Ctrl + Shift + Enter, because it must be entered as an array formula.
Option 4. Incomplete full name, there are “expats”
If the list may contain expats or non-standard names that are not in the directory, then the previous formula will automatically classify a person as a man, which is not good. Therefore, for complete universality, you can add a directory of male names and one more check, as we already did in option-2:
Much better now 🙂
- How to merge text from multiple cells into one
- Finding the closest similar text
- Separation of full name into separate columns