When creating a mail merge, it’s often necessary to separate the first and last name of a full name in Google Sheets, so that you can use the first name in your mail merge.
There are multiple ways to split out the first and last name in a Google Sheet, and we’ll describe some of the easiest ways below. Let’s say that we’re starting off with a Google Sheet that has a “Name” column:
Option 1: Data > Split text to columns
This option will split the “Name” column into two columns, so you may want to move your “Name” column to the far right of your sheet to prevent the column on the right of it from being obliterated.
1. Click “Data > Split text to columns”
2. Choose “Separator: Space”
3. The column will be split into two columns with the first name and last name:
Option 2: Use the INDEX and SPLIT functions
1. Add two new columns named “First Name” and “Last Name”
2. In the First Name column, put =INDEX(SPLIT(A2," "),0, 1)
Replace A2 with the cell of the first Name value, then drag the small blue box on the First Name cell down to fill in the rest of the cells.
3. In the Last Name column, put =INDEX(SPLIT(A2," "),0, 2)
Similar to the First Name column, you’ll want to drag this down to fill in the other Last Name cells below it.
Note that this formula is almost the same as the one for First Name, except that it has a 2 instead of a 1, which means it’ll take the second value of the split instead of the first value.
Option 3: Use the Split Names add-on
1. Install the Split Names add-on for Google Sheets
2. Select the Name column
3. Click “Add-ons > Split Names > Start”
4. Follow the directions to split the Name column into two columns