In this tutorial, you will learn how to combine first and last name in google sheets.
Google Sheets makes it easy to import data from a number of different sources such as CSV files from other applications and Google Forms. While this can be very convenient, often the imported data isn’t in quite the right format for its intended use inside Google Sheets. For example, it’s common to receive data with first and last name in separate columns, but often we may need a single column for full name. Before you begin manually combining first and last name in Google Sheets, read the steps below to learn how to use formulas to do the work for you.
Using & Operator to Combine Text Strings
The formula to combine first and last names (or any other text) is very simple. Here’s how to use it:
Create a blank column for the full names. It will be easier if it’s near the columns containing the first and last names. For this example, we’ll assume the first and last names are in columns A and B respectively
In the first cell of the full name column, type the following formula: “=A2 & ” ” & B2”, where A2 and B2 are the locations of the first and last names for the current row. Be careful to include the space between the quotation marks in the formula, or the first and last names will be joined without a space between them
Hit enter to finalize the formula. If you have autocomplete on, you may get a popup asking if you’d like to fill the formula down. Go ahead and click the checkmark to accept the suggestion
If you don’t have auto complete turned on, drag the formula down or copy paste as needed
Making an Array Formula
The last few steps of the above section underline a drawback to using standard formulas: It can be cumbersome to ensure the formula is everywhere it needs to be, especially if the number of names changes over time. Fortunately, array formulas can get around this issue by generating data for an entire column at a time. Here’s how to use an array formula to update the above method:
In the first cell of a blank column, type the following: “=ARRAYFORMULA(A2:A & ” ” & B2:B)”. In this formula, the ranges A2:A and B2:B specify the columns for first and last names. The arrayformula goes down the rows of the spreadsheet and combines the data from the two columns. Eg., A2 with B2, A3 with B3, etc down to the last row. In this example, the arrayformula is in row 2, but if yours is in a different row, be sure to update the ranges by replacing the 2s with the appropriate row number
Hit enter to complete the formula and the combined names will populate down the entire column. When new first and last names are added, the array formula will update accordingly
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to combine first and last name in google sheets. Want more? Check out all the Google Sheets Tutorials.