7 Ways to Combine First and Last Names in Microsoft Excel
Meet Bhaskar, your friendly neighborhood tech enthusiast-turned-wordsmith. For the past few years, he’s been your go-to guide for demystifying iOS, Android, macOS, and Windows platforms. While he holds a B.Tech degree, he has dedicated himself to simplifying tech complexities for all and has lent his expertise to publications like iGeeksBlog, The Writing Paradigm, and more. His forte lies in crafting easy-to-follow how-to guides and in-depth articles, making the intricate world of technology accessible to all. When not immersed in the tech world, you’ll find him enjoying music or sports.
Afam has been a content writer with Guiding Tech Media since August 2023. However, his experience in tech writing dates back to 2018, when he worked as a freelance writer for Make Tech Easier. Over the years, he has grown a reputation for publishing quality guides, reviews, tips, and explainer articles. His work is featured on top websites, including Technical Ustad, Windows Report, and Next of Windows. He has training as a Microsoft Certified Professional and has covered more Microsoft-related guides on these platforms.
Method 1: Using Flash Fill
Flash Fill is an intelligent tool in Excel that eliminates the need for complex formulas and manual data manipulation. It is the easiest solution if you want to merge many names.
Step 1:Launch yourdocument.
Step 2:Manually input the value for the first one or two roles, combining the first and last names for two roles.
Step 3:Go to the next cell and start typing the next person’s full name. Excel will show you suggestions based on your previous output. HitEnterto accept the suggestions.
Step 4:If you can’t see suggestions, go to theDatatab and clickFlash Fill.
Tip:Press Ctrl + E on your keyboard to trigger the shortcut. Also, you mayfollow special stepsif you notice the slow Excel response time.
Method 2: Using the & (Ampersand) Symbol
The ampersand symbol (&) acts as a catalyst, facilitating the amalgamation of disparate data. You can place it between the cells containing the first and last names, and Excel will seamlessly merge the content into a single cell. Adhere to the steps below to use this method:
Step 1:Launch your desiredExcel document.
Step 2:Click into thefirst cellwhere you want the merge to occur. Type the command below and hitEnter. We used A2 and B2 because the first name is in cell A2, and the last name is in cell B2.
Step 3:Now, to use thesame formulafor all cells, click on the bottom right of the cell and drag thesmall square downto copy it into all the remaining cells.
Method 3: Via the CONCAT Function
CONCAT essentially means combining or joining things together. The CONCAT function in Excel allows you to merge different text strings into one effortlessly. Here’s how to use it:
Step 1:Launch theExcel filewith the names that need merging.
Step 2: Verifythat the first names are in one column and the last names are in another. Double-click thecellwhere you want the combined full name to appear.
Step 3:Type the command below and hitEnter. We used A2 and B2 because the first name is in cell A2, and the last name is in cell B2.
Step 4:To apply the formula to other cells, use theAutoFill handle. Hover over the bottom right of the cell with the formula until you see a small square (the fill handle). Then, drag it down or across to copy the formula to adjacent cells.
Also Read:How to compare columns in Excel for matches
Method 4: Using the CONCATENATE Function
This works very similarly to the CONCAT function. However, it may allow you to join more than two strings.
Step 1:Launch theExcel filewith the names that need merging.
Step 2: Verifythat the first names are in one column and the last names are in another. Double-click thecellwhere you want the combined full name to appear.
Step 3:Type the command below and hitEnter. We used A2 and B2 because the first name is in cell A2, and the last name is in cell B2.
Step 4:To apply the formula to other cells, use theAutoFill handle. Hover over the bottom right of the cell with the formula until you see a small square (the fill handle). Then, drag it down or across to copy the formula to adjacent cells.
Method 5: Using the TEXTJOIN Function
The TEXTJOIN function in Excel can combine text from multiple ranges or individual text strings based on a specified delimiter. Here’s how to use it:
Step 1:Open thespreadsheetto merge the first and last name columns by double-clicking on it.
Step 2:Head to the cell where you want the combined names to start and input the formula below. We used A2 and B2 because the first name is in cell A2, and the last name is in cell B2.
Step 3:Drag thefill handle(a small square at the bottom-right corner of the cell) down to apply the formula to the entire column. This will combine all first and last names.
Note:TRUE indicates that the function should ignore empty cells.
Method 6: Via Power Query
Power Query is your trusty sidekick that helps clean, shape, and merge your data effortlessly. Adhere to the steps below to merge first and last name columns in Excel using Power Query.
Step 1:Launch theExcel filewith the names for merging.
Step 2:Select the range of cells containing the first and last names. Go to theDatatab and clickFrom Table/Rangeto open the Power Query Editor. SelectOKwhen prompted.
Step 3:Choose the first and last names columns in the Power Query Editor. Click on theAdd Columntab > selectMerge Columns.
Step 4:Choose a delimiter (e.g., a space) for combining names.Renamethe column and clickOK.
Step 5:Close the window by pressing theXicon. When prompted, selectKeep.
Your data with merged first and last names is now ready in Excel and will be shown in a new column.
Method 7: Using a VBA Script
A custom script offers a powerful and flexible way to combine first and last names in Excel, especially for experienced users comfortable with VBA. This method is especially useful for large datasets or complex formatting requirements, allowing for precise customization in how the names are merged.
Step 1:Open theExcel workbookwhere you want to combine first and last names.
Step 2:PressAlt+F11to open the Visual Basic for Applications (VBA) editor.
Step 3:In the VBA editor, right-click anyitemin the Project Explorer window on the left. ChooseInsert> selectModule.
Step 4:Copy and paste the code below and close the window to save it.
Note:Adjust the sheet name and column references (Sheet1, columnsA,B, andC) to match your actual data layout in Excel. Also, ensure that your data starts from row 2, assuming row 1 contains headers.
Step 5:PressAlt+F8on your keyboard. SelectCombineNames(or whatever you named the macro) and clickRun.
Also Read:How to view multiple worksheets side-by-side in Excel
Was this helpful?
Last updated on 29 August, 2024
Leave a ReplyCancel reply
Your email address will not be published.Required fields are marked*
Name*
Email*
Comment*
Δ
Read Next
The article above may contain affiliate links which help support Guiding Tech. The content remains unbiased and authentic and will never affect our editorial integrity.
More in Mac
4 Fixes for the Headphone Jack Not Working on Mac
How to Fix “This Setting Has Been Configured by a Profile” on Mac
JOIN THE NEWSLETTER
Get Guiding Tech articles delivered to your inbox.
© 2024 Guiding Tech Media. All Rights Reserved.
