Fortunately there is a formula to combine data (similar to a subtraction formula, or other type of formula that compares values) that you can use which will allow you to combine first and last names into one cell in Excel 2013. It is the CONCATENATE formula, and it can be very helpful in eliminating the need for a lot of unnecessary manual data entry.
Summary – How to combine first and last name in Excel
We also explain the steps below with pictures, if you would prefer to see the instructions in that format. The formula in this guide has some other uses as well. Check out our how to combine three columns in Excel tutorial for more on this topic.
Merge First and Last Name Cells Into One Cell in Excel 2013
The steps below are going to use the concatenate function to combine a cell with a first name and a cell with a last name into one cell. If you delete the original, separated cells, then the data in the combined cell will be deleted as well. If you want to delete the original cells and keep the combined cell, then you will need to use the paste as values option. We use Microsoft Excel 2013 for for this guide, but these steps will also work in most other versions of Excel. Step 1: Open your spreadsheet in Excel 2013. Step 2: Click inside the first cell in which you want to display the combined data. I’ve created a new full name column for this purpose. Step 3: Type =CONCATENATE(XX, " “, YY) into the cell. Replace XX with the cell location of the first name, and replace YY with the cell location of the last name. Note that there is a space between the quotation marks in the middle part of the formula. This will insert a space between the combined first and last names. In the example image below, the formula, as shown in the formula bar and the cell, is =CONCATENATE(A2, " “, B2) Step 4: Press Enter on your keyboard to execute the formula. You should now see the single full name value to the right of the separate name columns. Step 5: Click on the cell with the formula that you just created, click on the fill handle at the bottom-right corner of the cell, then drag it down to fill all of the cells for which you want to combine the names. Note that you can also copy the formula then paste it into these cells instead. Excel will automatically update the formula so that it displays the combined cells for that row. Question 1 – Will the combined name update if I change the first or last name? Answer 1 – Yes, the cell containing the full name will update if you make a change to a first or last name in a cell. The full name cell is being populated by a formula, and part of that formula involves checking a specified cell for its current value. Question 2 – Is there a way that I can update the full name cell so that it won’t change if I make a change in one of the first or last name cells? Answer 2 – Yes, you can accomplish this by selecting all of the cells containing values that you don’t want to update, pressing Ctrl + C to copy them, then clicking the Paste button in the ribbon and selecting the Paste as Values option. This will replace the formulas with their values. Question 3 – Can I split a column of full names into First Name and Last Name Cells? Answer 3 – Yes, you can do this with the Text to Columns option on the Data tab. This assumes that all of the names consist of just two words. Note that you can end up with more than one extra column of data if any of the names are three words. Excel splits data into separate cells each time it encounters a space. If you have names with more than two words you will need to create an additional empty column for whatever the highest word count is among your full names. You will then need to combine these extra columns back into the appropriate columns for your needs. Question 4 – Is there another formula that I can use to combine first and last names in Excel besides concatenate? Answer 4 – Yes, you can also combine first and last names in Excel using the formula below: =XX&” “&YY Note that the only space in the formula is between the ampersands. That space is what adds the space between the names in the full name field. be sure to replace the XX part of the formula with the cell containing the first name, and the YY part of the formula with the cell containing the last name.
Quick Tricks
This concatenate formula can be modified in a number of different ways if your needs are different than those we created in the result above. For example, if you don’t want your first and last names separated by a space, you can replace the " " part of the formula with “.” if you wanted a period instead of a space. This concatenate formula will also help fix other similar fields that you may find in a spreadsheet that contains first and last names. For example, if you need to turn a first and last name into an email address, you could modify the code to be =CONCATENATE(XX, YY, “@gmail.com”) and wind up with an email address that contains the person’s first and last name with an @gmail.com email domain attached to it. There are a lot of other useful formulas in Excel 2013. Learn more about creating formulas in Excel to see what sorts of options you can employ to make some of your tasks a little easier. After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites. His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well. Read his full bio here.
You may opt out at any time. Read our Privacy Policy
