With a first name in A2 and a last name in B2, enter =A2&" "&B2 in another cell to combine them with a space. For example, if A2 contains Jane and B2 contains Smith, the result is Jane Smith. You can then fill the formula down to combine the names in other rows.
What You’ll Need
- An Excel workbook with the two parts of each name in separate cells, such as first names in column A and last names in column B.
- An empty destination column for the combined names. Keeping the original columns is useful if you later need to sort, correct, or reuse the separate name parts.
- Microsoft Excel for Microsoft 365 on Windows. The basic formula also works in Excel for Mac and Excel for the web, though ribbon labels and keyboard shortcuts can differ slightly.
Step-by-Step
- Check how the names are arranged. Look at one row and identify the cell holding each name part. In this example, A2 contains the first name and B2 contains the last name. The formula must refer to the cells in the correct order; if your first and last names are in different columns, use those addresses instead. Confirm that both cells belong to the same person or record before combining them.
- Choose an empty cell for the result. Click a blank cell in the same row, such as C2. Do not enter the formula over either source cell, because doing so can replace a name you still need. If column C already has data, choose another empty column or insert a new one by right-clicking its column letter and selecting Insert.
- Enter the basic formula. With the first name in A2 and the last name in B2, type
=A2&" "&B2in C2 and press Enter. The ampersand joins text, and the quoted space adds a visible gap between the two cell values. If A2 is Jane and B2 is Smith, C2 displays Jane Smith. Without the quoted space, the result would be JaneSmith, which is harder to read. - Check the result before applying it to the list. Compare the combined value with the two original cells. Check capitalization, spelling, and the space between the parts. If it looks correct, keep going. If the order is wrong, edit the formula so it refers to the last-name cell and first-name cell in the order you want. The displayed result is text built from the current contents of the source cells.
- Fill the formula down the column. Select C2, then point to the small square at the lower-right corner of the cell border, called the fill handle. Drag it down through the rows that contain names. Excel adjusts the references as it copies the formula: the next row refers to A3 and B3, then A4 and B4, and so on. If the neighboring columns have uninterrupted data, you can also double-click the fill handle to fill down alongside them.
- Verify several rows. Check the first filled row, a row near the middle, and the last row. Make sure each result combines names from the same row rather than shifting or skipping a record. If you have a filtered list or gaps in the source data, dragging the fill handle lets you control exactly how far the formula is copied.
- Keep formulas or save fixed text, as needed. A formula result updates when either source cell changes. That is useful while names are still being corrected. If you need a fixed list that will not change when the source values change, copy the results and use Home > Paste ▼ > Values to paste the displayed text over a separate destination area. Check that the pasted names remain correct before removing or changing the source columns.
The basic formula uses relative references, so Excel changes A2 and B2 to the corresponding cells on each row when you fill down. You normally should not add dollar signs to those references: a reference such as $A$2 stays locked on row 2 and would repeat the same name in every result. If you are using an Excel table, you can also enter a formula in the first cell of a new table column; Excel often fills the formula down the whole table column automatically.
Common Mistakes
- Leaving out the separator. Joining the cells directly creates a result like JaneSmith. Include a space inside quotation marks between the references. If you want a comma, hyphen, or another separator instead, put that character inside the quotation marks, for example a comma followed by a space.
- Putting quotation marks around cell references. A formula such as
="A2 B2"displays those characters as literal text; it does not read the values in A2 and B2. Use cell references without quotation marks, and use quotes only for text you want Excel to insert, such as the separating space. - Combining cells from different rows. If a formula in C2 refers to A2 and B3 by mistake, it can pair two different people. Inspect the cell addresses in the formula bar and check that the row numbers match.
- Filling too far or not far enough. Dragging the fill handle past the last record can create formulas beside blank rows, while stopping early leaves names uncombined. Use the actual extent of your list and verify the last result.
- Overwriting source data too soon. A combined name does not preserve the original first-name and last-name values as separate fields. Keep a copy of the workbook or retain the source columns until you have checked the combined results and confirmed that you no longer need the separate values.
Tips
If some rows may have an empty name cell, a simple join can leave an unwanted leading or trailing space. With the first name in A2 and the last name in B2, use =TRIM(A2&" "&B2) to remove extra spaces at the beginning and end of the combined text and reduce repeated spaces between words. TRIM is helpful when source cells contain accidental spaces as well as when one of the two cells is blank. It does not correct misspellings or change the capitalization of names.
For a three-part name, decide the order and separators before building the formula. With a first name in A2, middle name in B2, and last name in C2, enter =A2&" "&B2&" "&C2 in D2. If all three cells contain Jane, Ann, and Smith respectively, the result is Jane Ann Smith. If middle names are optional, consider the TRIM approach so a blank middle-name cell does not leave extra spaces.
You can also use a separator other than a space. With the first and last names in A2 and B2, enter =A2&", "&B2 if you need a result such as Jane, Smith. To create a label in last-name-first order, use =B2&", "&A2; for Smith, Jane, that format can be useful in a list sorted or displayed by surname. The source cells remain unchanged either way.
Use the formula bar to review a result if the cell is too narrow to display the whole name. You can widen the column by dragging the boundary between its column letters, or double-click that boundary to AutoFit the column width. Widening a column changes only the display, not the text stored in the cell. If you want to preserve the formula for later updates, save the workbook normally; formulas recalculate as their source cells change.
On Windows, Ctrl+C copies selected cells and Ctrl+V pastes them; Ctrl+Z undoes an action. On a Mac, use Command+C, Command+V, and Command+Z. These shortcuts are handy when copying results or undoing an accidental edit. Excel for the web supports the same basic formula and fill-handle method, but menus and available paste options can look different depending on the browser window.
Troubleshooting
- The cell displays the formula instead of a name. The cell may be formatted as Text, or Excel may be treating the entry as text. Change the cell format to General using Home > Number Format, then edit the formula and press Enter again. Also check that the formula starts with an equals sign and that Show Formulas is not enabled. In Excel for Windows, Ctrl+` toggles formula display; on many Mac keyboards, the equivalent key combination depends on the keyboard layout.
- The result has no space or shows an unexpected character. Check the quoted separator in the formula. The basic space separator is exactly
" ", with one space between the quotation marks. If you see a comma or other character, that character may be part of the formula or one of the source cells. - A result begins or ends with a blank. One of the source cells may be empty or may contain extra spaces. Use the TRIM formula above and inspect the source values. If a cell appears blank but contains a space, deleting that space from the source can also resolve the issue.
- Every row shows the same name. Look for fixed references such as
$A$2and$B$2. Those references remain pointed at row 2 when filled down. For a normal list, use relative references such as A2 and B2 so each row uses its own values. - The formula returns an error or does not calculate. Confirm that the cell references point to valid cells and that the formula has balanced quotation marks and parentheses. The simple ampersand formula does not require parentheses. If calculation appears paused, check Formulas > Calculation Options and select Automatic. If you copied a formula from a web page, retype its quotation marks as straight double quotes.
Frequently Asked Questions
Can I combine two names without a formula?
Yes. You can type a combined name manually, but that will not update if either source cell changes. A formula is better for a list that may need corrections or regular updates. You can paste the formula results as values later if you need fixed text.
Will the formula change my original names?
No. It reads the values in the source cells and displays a combined result in the destination cell. The original cells stay separate unless you overwrite them yourself.
How do I combine names when one cell might be blank?
With the first name in A2 and last name in B2, use =TRIM(A2&" "&B2). TRIM removes extra spaces at the edges, so the result does not begin or end with the separator when one name part is empty.
Can I combine first, middle, and last names?
Yes. Join each cell reference in the desired order and place a quoted space between references. For an optional middle name, use TRIM around the joined text to clean up spaces if that middle-name cell is blank.
How do I keep the combined names from changing?
Copy the completed results, then paste values into a destination range using Home > Paste ▼ > Values. The pasted text no longer depends on the original cells, so it will not update when those cells change.
Does this work in Excel for Mac or Excel for the web?
Yes. The ampersand formula works in current Excel for Windows, Mac, and the web. The main differences are the appearance of the menus and some keyboard shortcuts, not the formula itself.
Related
- Center in Excel Without Merging: A Practical Guide
- How to Center an Image in Excel on a Worksheet or Page
- How to Center Anything on a Page in Excel for Printing
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP With Multiple Criteria: Formulas and ExamplesTo look up a row that meets multiple criteria in Excel, multiply the criteria tests inside XLOOKUP: =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:C100,"Not…
- LEFT in Excel: Extract Text from the StartIn Excel, use the LEFT function to return characters from the beginning of a text value: with text…
- Calculate Hours in Excel: Formulas and Time FormatsTo calculate elapsed hours in Excel, put the start time in A2 and end time in B2, then…
- Gross Margin Excel Calculation: Formula and ExamplesWith revenue in B2 and cost of goods sold (COGS) in C2, calculate gross margin in Excel with…
- Excel Formula for Last 4 Digits: RIGHT and FixesTo return the last four characters from a value in cell A2, use =RIGHT(A2,4). For example, if A2…
- Excel Time Formulas: Calculate Overnight PM-to-AM HoursIf A2 contains a PM start time and B2 contains the following AM finish time, enter =MOD(B2-A2,1) to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.