Have to wrangle data on people's names for event registration, employees, etc? It can be super helpful to keep first/last names and titles in separate columns while also having them display combined into one. If you've worked with registration data for an event or manage employees or speakers or sponsors, sometimes, you'll run into this problem where you have these multiple columns of data and you need those separate columns of data for first name, last name, title, et cetera. But at the same time, you want all of those combined into one and you want them combined intelligently. Here's how to make that happen in Google Sheets, Excel, and Airtable!
Google Sheets Template: https://docs.google.com/spreadsheets/d/13DEAySWkis0AyIsWSEcaWm2-OGfzgXPHcttwqKZ1Zs0/copy
Google Sheets Formula:
This assumes you have “Full Name” in column A, “Formal Title” in column B, and First Name, Last Name, and Post-nominals in columns C, D, and E (respectively), and that your data starts in row 2. You can then copy/paste this formula or drag it down below that for however many rows you need.
=TRIM(B2)
&IF(AND(B2<>"", OR(C2<>"", D2<>"", E2<>"")), " ", "")
&TRIM(C2)
&IF(AND(C2<>"", D2<>""), " ", "")
&TRIM(D2)
&IF(AND(OR(B2<>"", C2<>"", D2<>""), E2<>""), ", ", "")
&TRIM(E2)
This formula looks to see if there is anything in B2, C2, D2, or E2. If so, it TRIMS them and displays them (removes spaces anywhere but in-between words). It then adds a space and/or comma at the appropriate spots based on United States naming conventions.
Excel Template: https://docs.google.com/spreadsheets/d/1L89xPf6WLMeawrjHtAJrPs4bco2sRuc7/copy
Excel Formula:
This assumes you have “Full Name” in column A, “Formal Title” in column B, and First Name, Last Name, and Post-nominals in columns C, D, and E (respectively), and that your data starts in row 2. You can then copy/paste this formula or drag it down below that for however many rows you need.
=TRIM(B2)
&IF(AND(B2<>"", OR(C2<>"", D2<>"", E2<>"")), " ", "")
&TRIM(C2)
&IF(AND(C2<>"", D2<>""), " ", "")
&TRIM(D2)
&IF(AND(OR(B2<>"", C2<>"", D2<>""), E2<>""), ", ", "")
&TRIM(E2)
This formula looks to see if there is anything in B2, C2, D2, or E2. If so, it TRIMS them and displays them (removes spaces anywhere but in-between words). It then adds a space and/or comma at the appropriate spots based on United States naming conventions.
Airtable Template: https://airtable.com/shrSsugLV1ecMOeuM/tblz8BfRTow77RL4v/viw5lQkeOTSH9Xzya
Airtable Formula:
TRIM({Formal Title})
&IF(AND({Formal Title}!="", OR({First Name}!="", {Last Name}!="", {Post-nominals}!="")), " ", "")
&TRIM({First Name})
&IF(AND({First Name}!="", {Last Name}!=""), " ", "")
&TRIM({Last Name})
&IF(AND(OR({Formal Title}!="", {First Name}!="", {Last Name}!=""), {Post-nominals}!=""), ", ", "")
&TRIM({Post-nominals})
This formula looks to see if there is anything in the fields “Formal Title,” “First Name,” “Last Name,” and “Post-nominals.”If so, it TRIMS them and displays them (removes spaces anywhere but in-between words). It then adds a space and/or comma at the appropriate spots based on United States naming conventions.
