exceltestpracticeStart free
Practice testsLearnFunctionsShortcutsPricingStart free

How-to guides

How to combine first and last names in Excel

Join a first-name column and a last-name column into one full name, with the right spacing, order and handling for missing parts.

A full-name column is one of the most common small jobs in a spreadsheet: first names live in one column, last names in another, and a mailing list or report wants them together.

The formula is short. The detail that matters is the space in the middle, then what to do about blanks and about the order the person asked for.

Steps in Excel for Windows

Shortcut: Ctrl+E (Flash Fill)

  1. Click the first cell of the new column. Type the formula in the row of the first person, for example C2 when names start in row 2.
  2. Join with an ampersand and a space. Type =A2&" "&B2 and press Enter. The ampersand joins text and the quoted space goes between the two names. Without the space you get AnaRuiz.
  3. Fill down. Double-click the small square at the bottom-right corner of the cell, or drag it down, to copy the formula to every row.
  4. Change the order if the task asks. For Last, First use =B2&", "&A2. Everything in quotes appears exactly as typed, including the comma and the space after it.
  5. Turn the formulas into fixed text if needed. Select the new column, copy it, then Paste Special, Values. Now you can delete the original two columns without breaking the full names.
  6. Or let Flash Fill do it. Type the full name for the first row by hand, start the second, and press Ctrl+E. Excel guesses the pattern from your examples. Check every row, because it is a guess, not a rule.

Menu names are from Microsoft 365 on Windows. Excel for Mac and Excel for the web put the same commands in slightly different places.

Worked example

1. First and last with a space between

First and last with a space between. The formula =A2&" "&B2 in cell C2 returns Ana Ruiz.
ABC
1FirstLastFull name
2AnaRuizAna Ruiz

The formula in C2 joins A2, a quoted space, and B2.

2. Last name first

Last name first. The formula =B2&", "&A2 in cell C2 returns Ruiz, Ana.
ABC
1FirstLastFull name
2AnaRuizRuiz, Ana

Swap the order and put a comma and space between the pieces.

3. A blank middle name without a double space

A blank middle name without a double space. The formula =TRIM(A2&" "&B2&" "&C2) in cell D2 returns Ana Ruiz.
ABCD
1FirstMiddleLast
2AnaRuizAna Ruiz

Joining three cells with spaces leaves two spaces in a row when B2 is empty. TRIM collapses them, so the result has a single space.

4. TEXTJOIN skips empty cells

TEXTJOIN skips empty cells. The formula =TEXTJOIN(" ",TRUE,A2:C2) in cell D2 returns Ana Ruiz.
ABCD
1FirstMiddleLast
2AnaRuizAna Ruiz

TEXTJOIN takes the separator first, then TRUE to ignore empty cells, then the range. No TRIM is needed. It is available in Excel 2019 and Microsoft 365.

Common mistakes

  • Forgetting the space. =A2&B2 gives AnaRuiz. The space is its own piece of text and has to be inside quotation marks.
  • Typing the name instead of the formula. Hand-typed full names do not follow the data. A prompt that says "use a formula" wants the ampersand, CONCAT or TEXTJOIN.
  • Deleting the source columns too soon. The joined formulas read from the first and last name cells. Delete them and the results become #REF!. Paste the joined names as values first.
  • Stray spaces in the source. A trailing space in the first name leaves a double space in the result. Wrap the pieces in TRIM when the data is not clean.
  • Trusting Flash Fill without checking. Flash Fill spots a pattern from a few examples. A row with a suffix such as Jr. or an unusual order can come out wrong, and the results do not update if the source changes.

On Excel job tests

The prompt may read "combine the first and last names into a Full Name column" or "create an email address from the first initial and last name". Both are joins: the ampersand, CONCAT or TEXTJOIN, with LEFT for an initial.

If the sample shows a comma, such as "Ruiz, Ana", copy that punctuation and spacing exactly, because "Ruiz,Ana" and "Ruiz, Ana" are different text.

  • CONCATJoins text from several cells or ranges into one string.
  • TEXTJOINJoins text from a range with a delimiter between each item, optionally skipping blanks.
  • TRIMRemoves extra spaces from text, leaving single spaces between words.
  • UPPERConverts text to uppercase.
  • split full names into first and last name in ExcelBreak a Full Name column into First and Last columns with a formula that keeps working, and handle middle names that break the simple version.
  • use Text to Columns in ExcelSplit text in one column into separate columns at a comma, space, tab or other delimiter, with the Text to Columns wizard on the Data tab.

Questions

What is the difference between CONCATENATE, CONCAT and the ampersand?
All three join text. CONCATENATE is the older function, CONCAT replaced it and accepts ranges, and the ampersand is the shortest. They give the same text for two cells.
How do I add a space between first and last name?
Put a space in quotation marks between the cells, as in =A2&" "&B2.
How do I keep the full names after deleting the original columns?
Copy the joined column and use Paste Special, Values on the same cells. They become plain text that no longer depends on the originals.
How do I split a full name back into two columns?
Use Text to Columns with a space delimiter, or LEFT and FIND formulas. See the guide on splitting full names.

Practice this

No free lesson covers joining text yet. The closest practice is the free 5-item sampler, which uses the same kind of tasks in a working grid.

Try a 5-item Excel test sample