Extract Last, First Middle name 🇬🇧

June 22, 2023 - u/FaeryFluff (see Quick answer)

Hi everyone

Firstly, so relieved r/Notion is back! I was practically weeping every time I Googled my formula problems and couldn't get in!

I'm hoping someone can help me out with a formula situation:

The title property of my database has the structure Surname, First Name Middle Names (YYYY). For example:

Smith, Clara Jane (1952) or Adams, Harry (1922) or Jones, Sarah May Alice (2000)

(as you can see, a person could have no middle name, one middle name, or multiple middle names).

Extract the Surname (i.e. word before the comma) out to a separate Surname property

Extract the first name (i.e. word after the comma and the space) to a First Name field

Extract all names following the First Name (i.e. the second space) to a Middle Names field. (Those middle names don't have to be separated...but they could be, I suppose!)

Extract the year from between the brackets, to a property called Birth Year.

My formula-writing experience is mid-to-upper beginner level, so I've managed the surname extraction part using replaceAll. But I'm having trouble extracting the rest in the order I need (well, specifically because it involves extracting based on spaces, and with the number of middle names being open-ended).

I found a great resource that covers this issue for a First Name, Middle Name(s), Surname structure...but my level of experience means that I can't figure out how to reverse engineer it into the Surname First, Then Other Names structure.

Please and thanks for your help!

June 22, 2023 2:27 PM (GMT+2) - u/Faeryfluff (see Advanced answer)

A question: you mentioned that in instances without the year, the formula would break. Occasionally, I may have a page where there is no year (e.g. it's unknown) - and therefore there are no brackets either. Could this be dealt with using an if(empty) statement, do you think?

Quick answer

<aside> <img src="https://prod-files-secure.s3.us-west-2.amazonaws.com/9210293a-6b0f-4638-8e8d-3c40b40ed42b/b8366055-a446-4861-aa67-1e926814ca74/Invisible.png" alt="https://prod-files-secure.s3.us-west-2.amazonaws.com/9210293a-6b0f-4638-8e8d-3c40b40ed42b/b8366055-a446-4861-aa67-1e926814ca74/Invisible.png" width="40px" /> Hi there! First of all, thank you for detailing your request. It helps a lot, most people don't do it and it's not clear what they want, or what exactly they're talking about.

You're going to need a bit of Regex. Your starting point is very clear and structured.

Surname, FirtName MiddleNames (Year)

You do need to work with the replace() function. This function takes the Title property as its first argument. The second element is a regular expression string that covers all possible cases. In the third part, we'll indicate what we want to retrieve.

I suggest the following expression string (you can build it differently, here's a quick example):

(.*), ([^ ]*) (.*)\$(\\d{4})\$

The different parts of your title are separated by parentheses and can be retrieved with the arguments $1, $2, $3 or $4.

Thus, the Surname is : everything before the comma and the space→ (.*),
The first name is everything after the surname and before the following space → ([^ ]*) (Will search for all characters that are not a space)
The following names, everything between first name and the space before the next part (.*)
The year, 4 digits between two parentheses→ \$(\\d{4})\$

Note : Double backslashes \\ are used to escape certain special characters.

<aside> ℹ️ It's important that all these elements come together to cover all the proposed structures (if one year is missing, for example, it won't work).

</aside>

Here is the final formula:

replace(prop("Name"), "(.*), ([^ ]*) (.*)\\((\\d{4})\\)", "$1")

Replace $1 by $2, $3 or $4 to get first name, following names or year.

</aside>

Advanced answer

For the formula to work with or without a date, the regular expression must be modified. It's not always a question of modifying the expression a little, sometimes you have to rethink the logic entirely. This is almost the case here. This took me longer than for the initial expression, as the new expression had to correspond to the two different structures. The differences are in purple.

The basis is the same, the :

Surname, FirtName MiddleNames (Year) **where **(Year) is an option

I suggest the following expression string (you can build it differently, here's a quick example):

(.*), ([^ ]*) **?([^\$]*)\\(?(\\d{4})?\$?**

The different parts of your title are separated by parentheses and can be retrieved with the arguments $1, $2, $3 or $4.

Thus, the Surname is : everything before the comma and the space→ (.*),
The first name is everything after the surname and before the following space **[But the space can be optional] → ([^ ]*) **?** (Will search for all characters that are not a space)
The following names, everything between first name and the space before the next part ([^\\(]*) This part of the expression is different.
The year, 4 digits between two parentheses [Optional] → \$?(\\d{4})?\$?

Note : ? means that the part of the expression may not exist.

Here is the final formula (takes into consideration that there may or may not be a date) :

replace(prop("Name"), "(.*), ([^ ]*) ?([^\\(]*)\\(?(\\d{4})?\\)?", "$1")

Replace $1 by $2, $3 or $4 to get first name, following names or year.

</aside>

Surname, First and Middlenames

By @Ouinx2 - **Buy me a Beer**