How to combine first and last name in Excel

This tutorial explores several methods for combining names in Excel: formulas, Flash Fill, and cell merging. Excel often stores first and last names in separate columns, but combining them into a single cell is frequently necessary. Manual merging is outdated; these techniques offer efficient alternatives.

Combining Names with Excel Formulas

The quickest method uses concatenation with the ampersand (&) operator or the CONCATENATE function.

Formula 1: Combining First and Last Names with a Space

Given first names in column A and last names in column B, the following formulas combine them in column C:

=A2&" "&B2 or =CONCATENATE(A2," ",B2)

This adds a space between the names. Copy the formula down to apply it to all rows.

Formula 2: Combining Last and First Names with a Comma

To merge names as "Last Name, First Name," use:

=B2&", "&A2 or =CONCATENATE(B2,", ",A2)

Formula 3: Combining First, Middle, and Last Names

With first, middle, and last names in columns A, B, and C respectively, use:

=A2&" "&B2&" "&C2 or =CONCATENATE(A2," ",B2," ",C2)

For cases where the middle name might be absent, use an IF statement:

=IF(B2="", A2&" "&C2, A2&" "&B2&" "&C2)

Formula 4: Combining First Initial and Surname

To create a short name, use the LEFT function to extract the first initial:

=LEFT(A2,1)&" "&B2 or =CONCATENATE(LEFT(A2,1)," ",B2)

Variations include adding a period (.) or omitting the space. Lowercase conversion can be achieved with the LOWER function.

Formula Tips:

• TRIM: Use TRIM(A2) to remove leading/trailing spaces before concatenation.
• PROPER: Use PROPER(A2) to capitalize names correctly.
• Convert Formulas to Values: Use "Paste Special" to convert formulas to static values.

Automatic Name Merging with Flash Fill

Flash Fill automates name merging based on a pattern. Manually enter the combined name format for the first row, and Excel will suggest the rest. This method respects capitalization and punctuation.

Merging Cells to Combine Names

The Ablebits Merge Cells tool allows merging columns while retaining all values. Select the name columns, choose "Merge Columns into One," specify a space as the separator, and click "Merge."

This tutorial provides various methods to efficiently combine names in Excel, catering to different needs and preferences. Choose the method best suited to your data and desired outcome. Downloadable examples are available.

The above is the detailed content of How to combine first and last name in Excel. For more information, please follow other related articles on the PHP Chinese website!