How to Combine First and Last Name in Google Sheets

An endless list of contacts is an organizational challenge. Even worse, if you have to transfer the data between the tools and the efforts end with broken data and chaos. In this guide, we'll demonstrate how to effortlessly combine first and last name in Google Sheets to maintain consistency in data management. Easily adjust your spreadsheet to the requirements with these three simple methods.

Why do you need to combine first and last name in a spreadsheet?

There are complex scenarios in which you have to pull out the data from a CSV file or import them to external tools. It may happen when you have to upload the data to do some activities in bulk, e.g. generate certificates or create a mailing list. To do that without any errors, the spreadsheet has to be well-prepared first so that it covers specific requirements. Otherwise, the data may be incomplete or mismatched.

Method 1: Use the "&" Operator

The formula takes the first name from column A, adds a blank space (" "), and then adds the last name from column B. The ampersand formula is used to join these together.

#1 Open your Google Sheet file

Choose the file that contains the name and surname that you want to combine together.

#2 Choose an empty cell

Let's assume you're working with cells A2 (first name) and B2 (last name), and you want the full name in adjacent cell C2. Choose an empty cell from C2.

#3 Type the formula

In cell C2, type the ampersand function: =A2 & " " & B2. The space character is necessary to add the white space between the first and last name.

#4 Press Enter

The combined name should appear in C2.

#5 Apply the formula to the column

Double-click the bottom-right corner of C2 to apply this formula to the entire column, combining all names in the list. Alternatively, you can click the circle and drag it through the range of cells.

Method 2: Use the CONCATENATE Function

The CONCATENATE formula allows you to join multiple pieces of text together. You can use it to combine the first and last name from separate columns with a space in between.

#1 Open your Google Sheet file

Select the file that has the first and last names you wish to merge.

#2 Select an empty cell

If you have the first name in cell A2 and the last name in cell B2, and you want the merged name in C2, click on C2 to select it.

#3 Enter the CONCATENATE function

In cell C2, type the basic concatenation tasks: =CONCATENATE. Google Sheets automatically shows formula suggestions.

Then, click A2 add, and then type " ", plus click on B2. The result should be =CONCATENATE(A2, " ", B2). This beneficial function will merge the two names with a space between them.

#4 Press Enter

You should see the combined list of names in C2 now.

#5 Drag the formula down

To combine names for the single column, double-click on the small square at the bottom-right corner of C2 or click and drag it through the C column.

Done! The list of names includes both first and last names.

Method 3: Use the TEXTJOIN Function

TEXTJOIN allows you to specify a delimiter and whether or not to ignore empty cells. It offers more flexibility.

#1 Open your Google Sheet file

Navigate to the file that has the names you'd like to combine.

#2 Pick an empty cell

If you're working with A2 (first name) and B2 (column for surname), and you want the result in C2, click on C2 to highlight it.

#3 Input the TEXTJOIN function

Type this text in C2: =TEXTJOIN. Google Sheets automatically suggests the whole formula.

Then, type in =TEXTJOIN(" ", TRUE, A2, B2). The " " is the delimiter (space), and the TRUE option tells Google Sheets to ignore any empty cells.

#4 Press Enter

The merged name should now be visible in C2.

#5 Extend the formula

Double-click the bottom-right corner of C2 or drag it down to apply this formula to the whole column.

Method 4: Use Google Sheets AI feature

Google Sheets Smart Fill is an AI-driven handy function that revolutionizes spreadsheets. It recognizes patterns in your data, suggests formulas, and automatically fills columns based on the detected patterns. You can use it to combine first and last name in the spreadsheet as well!

#1 Open your Google Sheet file

Choose the file that contains the first and last names you want to merge.

#2 Manually combine two names

In the active cell, type a full name by combining a first name from column A and a last name from column B, e.g., "Emily Smith." Repeat this process for the next row in cell C3, like "Liam Johnson." This will help Google Sheets identify your pattern.

#3 Use the Smart Fill pop-up

When you begin typing another name in single cell C4, a Smart Fill pop-up should appear next to the formula bar. AI recognizes how you combined names from columns A and B, suggesting to you the formula that you can use to complete the task.

#4 Accept the AI suggestion

Click on the checkbox in the pop-up to accept Google Sheets' suggestion. After doing so, the autofill function will start working, and the neighboring cells in column C will automatically be filled with the combined first and last names.

#5 Enable Smart Fill (if necessary)

If you don't see the Smart Fill option and auto-fill pattern method – navigate to the "Tools" menu in Google Sheets.

From the dropdown, select the Autocomplete feature. Enable the options relevant to your needs. It's best to turn on all options for a full experience. 

Handy tips to combine cells in Google Sheets

There are some tips that can be deal-breakers while combining first and last names in Google Sheets. Check out this list and prevent any potential errors from happening.

Consistent format

Before combining, format all names consistently. For example, all cell names should start with a capital letter, and there should be no stray spaces before or after the names.

Automatic fill

After typing your formula in the first cell, you can double-click on the small square (fill handle) at the bottom-right of the cell to automatically apply the formula to the entire column based on the nearby data.

Trim extra spaces

Names in cells should not contain leading or trailing spaces. Use the TRIM function to remove any unwanted spaces before or after names: =TRIM(A2).

Check for errors

After combining names, it’s wise to scroll through the list and verify that no names have been missed or combined incorrectly.

Backup

Always keep a backup of your previous dataset. If something goes wrong, or if you want to revert back to the original state, you have your initial data safe and intact. In Google Sheets, you can go to File > Version history to track changes and revert to previous versions of your spreadsheet.

How to connect Certifier to Google Sheets?

Done! You've successfully merged the first and last names of your recipients, and now your file is ready to be linked with Certifier. Here are the simple steps you should follow to correctly upload the spreadsheet to Certifier.

Go to the Credentials tab.

Click the blue button at the top right corner: “Issue Certificates of Badges.”

Choose the group and add the recipients via the spreadsheet.

Upload recipient list.

First and last name merged

Structured and organized data in the spreadsheet is the way to optimize workflow efficiency. Once you know how to combine the first and last name in Google Sheets and have your dataset ready, it's easy to automate the process of data management and get all its benefits.

Don't hesitate to explore further and make the most of Certifier's versatile tool for certificate generation. Join today to take advantage of all the automation perks!