[ad_1]
To merge first and last names in Excel, type the first name combined as you’d like it to appear, then use Flash Fill to automatically combine the rest of the names. Alternatively, get finer control by combining your name cells using a formula or the Power Query tool.
Do you want to bring the first and last names that are in separate cells into a single cell? Combining text is easy in Excel and we’ll show you five different ways to combine names.
Method 1 – Use Instant Fill
Flash Fill is an Excel feature that automatically fills your cells by recognizing the fill pattern. You manually fill a cell with the type of data you want, and the function detects that data and fills all selected cells accordingly. No formula is needed with Flash Fill.
In the case of merging names, you manually merge the first and last names for a single record, and Flash Fill recognizes that pattern and merges the names from all your other records automatically.
To start using Flash Fill, in your spreadsheet, select the first cell where you want to display the combined names. In this cell, type the first and last name together as you want them to appear.
Go down a row and start typing the first and last name for your second record. As soon as you start typing, Flash Fill will detect the pattern and fill all your records in gray color. To confirm that you want to fill these cells, press Enter on your keyboard.
Advice: If an Instant Fill message does not appear, use the shortcut Ctrl+E to force an Instant Fill.
And that is. Excel automatically merged the first and last name from all your records.
Method 2: Use the symbol & (ampersand)
If you prefer to use an Excel formula to combine names, a simple method to bring the contents of multiple cells into a single cell is to use the & (ampersand) operator. This operator combines the contents of the specified cells, allowing you to combine all the data in the cells you want.
To use this method, in your spreadsheet, select the cell where you want to see the combined names.
In the selected cell, type the following formula and press Enter. Here, replace B2 with the cell containing the name and C2 with the cell phone that has the last name.
=B2&" "&C2
Notice that we’ve added a space between double quotes so that there is a space between the first and last names in the resulting cell.
In the selected cell, you will see the combined first and last name.
To copy this formula to all of your records, from the bottom right corner of the cell where you entered the formula, click and drag down covering all of your records.
If you have a middle name or initial in a separate column and want to merge them as well, add that cell as an argument to the formula. Here, we will merge the cell contents. B2 , C2 and D2 (in that order), with the contents of each cell separated by a space.
=B2&" "&C2&" "&D2
Alternatively, if you want to combine names so that the last name appears first followed by a comma, use a formula like this.
=D2&", "&B2&" "&C2
RELATED: How to combine two columns in Microsoft Excel
Method 3 – Use the CONCAT function
Excel CONCAT The function works the same as the & operator and allows you to combine the contents of multiple cells into a single cell. Using a function instead of an operator can sometimes make your formula easier for others to read and understand.
To merge your first and last name with CONCATIn your spreadsheet, select the cell where you would like to see the names combined.
In the selected cell, enter the following function and press Enter. In this function, replace B2 with your cell phone that has the first name and C2 with the cell containing the last name. Note that we have separated the first and last name with a space in this function.
=CONCAT(B2," ",C2)
Once you press enter, in your selected cell, you will see both names combined. You can copy the function to all your records by dragging down from the bottom right corner of the cell where the function is located.
If you want to merge the middle name or initial as well, use the function as follows. Here, C2 denotes the second name cell, while D2 contains the last name.
=CONCAT(B2," ",C2," ",D2)
If you want the last name to come first instead, try changing the formula and insert a comma like this:
=CONCAT(D2,", ",B2," ",C2)
And that is.
Method 4 – Use the TEXTJOIN function
With the TEXTJOIN function, you can combine their names using their specified delimiter. This function also offers the option to ignore empty cells. You will find that it is a bit more complicated to use than CONCATbut it is also a more powerful function.
Wear TEXTJOIN, in your spreadsheet, click the cell where you want to display the combined names. Then type the following function in the cell. Here, replace B2 with the cell phone that has the first name and C2 with the cell containing the last name. To ignore empty cells, change FALSE to TRUE .
=TEXTJOIN(" ",FALSE,B2,C2)
If your dataset has first, middle, and last name in the B2 , C2 and D2 cells, respectively, then you can merge them using the following function:
=TEXTJOIN(" ",FALSE,B2,C2,D2)
Method 5: Use Power Query
Power Query is an Excel feature that helps you combine data from multiple worksheets and workbooks. You can also use this option to merge your columns.
To use this method, first click on a cell in your dataset. Then, from the Excel ribbon at the top, select Data > From Table/Range.
From Table/Range.” width=”730″ height=”375″ onerror=”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);”/>
In the “Create table” box, make sure to specify the correct data set range. If your dataset has column headers, enable the “My table has headers” option. Then select “OK”.
A “Power Query Editor” window will open. Here, hold down Ctrl (Windows) or Command (Mac) and select your first and last name columns. Then right-click on any of the columns and choose “Merge Columns”.
In the “Merge Columns” window, click the “Separator” dropdown menu and choose “Space.” Optionally, in the “New Column Name” field, enter the name of the column that will have your names combined. Then select “OK”.
In the upper left corner of the “Power Query Editor” window, click “Close and Load”.
In your workbook, Power Query added a new worksheet that contains your join names.
You now have a complete spreadsheet with full, readable names for each entry. If you’re entering a lot of names and other data, learn the best Excel features for data entry.
RELATED: How to Combine Spreadsheet Data in Microsoft Excel
[ad_2]
