To combine first, middle, and last names in Excel, put them in A2:C2 in that order, then enter =TEXTJOIN(" ",TRUE,A2:C2) in D2. If A2 is Maya, B2 is Lee, and C2 is Chen, the result is Maya Lee Chen. TEXTJOIN inserts spaces and skips empty cells, so it is a practical choice for lists where some people do not have a middle name. You can also use the ampersand operator, Flash Fill, or Power Query, depending on whether you need a live formula or a one-time result.
What You’ll Need
- An Excel workbook with the names you want to join, ideally arranged in separate columns such as First Name, Middle Name, and Last Name.
- A blank destination column for the combined names. Keeping the original columns lets you correct or reuse the source data.
- Microsoft Excel for Microsoft 365 on Windows for the steps below. Most formula steps also work in Excel for Mac and Excel for the web; Flash Fill and Power Query availability or controls can vary by platform.
Before combining a large list, check that each column contains the intended part of the name and that the rows line up. A formula joins values from the same row; it cannot tell whether a first name and surname were accidentally placed on different rows.
Step-by-Step
- Check the source columns. Put each part of the name in its own column and add headers in the first row, for example First, Middle, and Last in A1:C1. Keep the first person’s details together on row 2, the next person’s details on row 3, and so on. If your data has only first and last names, those can be in A and B instead; adjust the formula to match the actual layout.
- Choose a blank result column. Click the first empty cell beside your data, such as D2. Do not type the combined result over one of the source names. Keeping the originals means you can change the joining method later, fix a spelling, or rebuild the output without re-entering names.
- Use TEXTJOIN for names with optional parts. With first, middle, and last names in A2:C2, enter
=TEXTJOIN(" ",TRUE,A2:C2)in D2. The first argument is the separator, a single space; TRUE tells Excel to ignore blank cells; and A2:C2 is the range to join. With Maya, a blank middle-name cell, and Chen, the result is Maya Chen—not Maya with an extra space before Chen. If all three cells contain Maya, Lee, and Chen, the result is Maya Lee Chen. - Copy the formula down the list. Select D2, point to the small square at the lower-right corner of the cell, and drag the fill handle down to the last row. You can also copy D2, select the destination cells below it, and paste. Excel adjusts the row references for each person, so the formula in D3 works with A3:C3. Check the first, middle, and last few rows to confirm the results still match the source data.
- Use an ampersand when the layout is simple. If first names are in A2 and last names are in B2, enter
=A2&" "&B2in C2. With Maya in A2 and Chen in B2, it returns Maya Chen. The ampersand joins the cell values, and the quoted space adds a separator. This method is easy to customize, but an empty cell may leave an extra space, so it is less convenient when your list includes optional middle names. - Handle blank name parts with TRIM if needed. With first, middle, and last names in A2:C2, enter
=TRIM(A2&" "&B2&" "&C2)in D2. If A2 is Maya, B2 is blank, and C2 is Chen, it returns Maya Chen. TRIM removes leading and trailing spaces and reduces repeated spaces between words to one. This is a useful alternative when you want to use ampersands but a middle-name field is sometimes empty. - Use Flash Fill for a quick, fixed result. If first names are in A and last names in B, type the desired combined name in C2, such as Maya Chen, then type the next correctly combined name in C3. Select C4 or the remaining destination range and choose Data > Flash Fill, or press Ctrl+E on Windows. Excel looks for a pattern in your examples and fills the remaining cells. Review the output: Flash Fill creates values rather than formulas, and it can guess incorrectly when names have inconsistent formats. On Mac, use Data > Flash Fill where available; the command and shortcut support can differ by Excel version.
- Use Power Query for a repeatable import process. If the names come from a table you will refresh regularly, select a cell in the source data and choose Data > From Table/Range. In Power Query Editor, select the name columns in the order you want, then choose Add Column > Merge Columns. Set the separator to Space, enter a new column name such as Full Name, and select OK. Choose Home > Close & Load to return the result to Excel. This creates a query output you can refresh when the source table changes; it is not the same as a cell formula that updates immediately as you type. Power Query authoring options vary in Excel for Mac and are more limited in Excel for the web.
For names with a title or suffix, decide where it belongs before filling the formula down. For example, if a title is in A and the first, middle, and last names are in B:D, include the title only if you want it in the finished display. The order in the formula or selected Power Query columns controls the output order.
Common Mistakes
Forgetting the separator: Joining A2 and B2 with no space between them produces MayaChen rather than Maya Chen. In an ampersand formula, include a quoted space between the cell references. In TEXTJOIN, put a space in the first argument.
Using a formula that cannot skip empty cells: Adding a space before and after every optional field can create a double space when one field is blank. Use TEXTJOIN with TRUE, or use TRIM around an ampersand formula. Note that TRIM is designed for ordinary spaces; unusual nonbreaking spaces copied from websites may need separate cleanup.
Overwriting the original names: Replacing first and last name columns with the combined result makes later corrections harder. Put the output in a new column and keep the source columns until you have checked the finished list and saved a copy if appropriate.
Expecting Flash Fill to stay linked: Flash Fill writes values, not formulas. If you change a source name afterward, the filled result will not automatically update. Choose a formula or a refreshable Power Query process when the output needs to track future changes.
Ignoring name order and punctuation: A Western-style first-middle-last order is not suitable for every naming convention. Decide whether your list should show Last, First, whether a comma is needed, and how to treat prefixes or suffixes before applying one pattern to everyone.
Tips
Convert the source range to an Excel table if you will keep adding people. Select a cell in the range and choose Insert > Table, making sure the table has headers. Tables make the columns easier to identify, and formulas entered in a table column generally fill down for the existing rows and new rows. Always confirm the formula is using the intended name columns after creating the table.
For a display such as Chen, Maya, use the surname first and add punctuation deliberately. With first names in A2 and surnames in B2, a formula such as =B2&", "&A2 returns Chen, Maya. Check that the comma and following space are inside the quotation marks. If you also need middle names, decide whether they should appear before or after the first name and adjust the order rather than changing the source data.
Keep formulas if the combined names should change when the source changes. If you need to send a static list to another system, first check the formula results; then copy the output column and use Home > Paste > Paste Values to replace the formulas with their current results. The original source columns can remain in the workbook as a record.
Use consistent source data where possible. Remove accidental spaces from imported names, check for cells that contain a full name instead of one name part, and look for suffixes such as Jr. or III in a separate field. A joining formula follows the cell contents literally—it does not understand which words are titles, surnames, or suffixes.
Troubleshooting
The combined name has no space. Check that the separator is present. For TEXTJOIN, confirm the first argument contains a space between the quotation marks. For an ampersand formula, confirm the formula joins the cells with &" "& rather than simply joining the two references.
There are two spaces or a space at the end. If you used ampersands and some name cells are blank, replace the formula with TEXTJOIN using TRUE, or wrap the joined text in TRIM. If the extra gap remains, check whether a source cell itself contains leading or trailing spaces or a nonstandard space character.
The formula appears as text instead of calculating. Select the cell and check Home > Number Format. If it is set to Text, change it to General, then re-enter the formula by pressing F2 and Enter. Also check that the formula begins with an equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles Show Formulas; on many Mac keyboards, the key combination differs, so check the Formulas tab or Excel keyboard settings.
The result shows #NAME? or another error. Confirm the function name is spelled TEXTJOIN and that the Excel version supports it. TEXTJOIN is available in Microsoft 365 and newer Excel releases. Check that the cell references point to the name columns, and use the comma argument separators shown here; if your regional Excel settings require semicolons, use the separator expected by that installation.
Flash Fill makes incorrect combinations. Undo the fill, provide two or more clear examples that show the exact pattern, then run Data > Flash Fill again. If names vary too much for Excel to infer a reliable rule, use a formula for each field or clean the exceptions manually. Do not assume a plausible-looking result is correct; compare it against the original columns.
Power Query does not show the expected output. Confirm that you selected the columns in the intended order and chose Space as the separator. If a new source row is missing, make sure it is part of the source table or range used by the query, then choose Data > Refresh All. If the query output is loaded elsewhere, locate its output table before checking the result.
Frequently Asked Questions
Can I combine first and last names without a formula?
Yes. Type the first desired result and another example in a blank column, then use Data > Flash Fill. It is fast for a one-time list, but the resulting values do not update when the source names change.
Which formula is best when some middle names are blank?
TEXTJOIN with a space separator and TRUE is usually the simplest choice because it ignores empty cells. TRIM around an ampersand formula is another option for ordinary spaces.
Will the combined name update if I edit the original cells?
A formula result updates when its source cells change. A Flash Fill result does not. A Power Query result changes after you refresh the query, provided the changed data is included in its source.
Can I combine names in reverse order?
Yes. Arrange the cell references in the order you want in the formula, or select the Power Query columns in that order. For Last, First, add a comma and a space between the surname and first name.
Can I combine more than three name columns?
Yes. Expand the range in TEXTJOIN to include the additional columns, or add the extra cell references to an ampersand formula. Check the order and separator so titles and suffixes appear where intended.
Can I use these steps in Excel for Mac or Excel for the web?
The formulas work in supported versions on both platforms, though regional settings may use different argument separators. Ribbon locations and the availability of Flash Fill or Power Query can vary, so use the corresponding Data commands shown in your version.
Related
- How to Change Zoom in Excel: Quick Steps and Fixes
- Change the Zoom Level in Excel: A Step-by-Step Guide
- Change Text to Lowercase in Excel: Steps and Tips That Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIFS in Excel with Dates: Examples and TipsTo sum values for dates in Excel, use SUMIFS with the amount range first and the date range…
- IFERROR in Excel: Leave Blank When a Formula ErrorsTo make an Excel formula show a blank instead of an error, wrap it in IFERROR and use…
- How to Filter Strikethrough Data in ExcelExcel’s regular filter menu can’t filter rows by strikethrough formatting. To show those rows, add a helper column…
- CAGR Calculation Formula in Excel: Examples and TipsIn Excel, calculate CAGR by dividing the ending value by the beginning value, raising that ratio to the…
- COUNTIF in Excel: Examples and How to Use It Step by StepTo count cells that match one condition in Excel, use =COUNTIF(A2:A11,"Apple"). With fruit names in A2:A11, this returns…
- Calendar Math in Excel: Dates, Workdays, and FormulasCalendar math in Excel means using real date values and date functions to add or subtract days, find…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.