Excel and Google Sheets how-to
Combine first and last name into one cell
First names in one column, last names in the next, and you need "Ana Lopez" in one cell. The & sign joins them.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | First | Last | Full name | |
| 2 | Ana | Lopez | Ana Lopez | |
| 3 | Raj | Patel | Raj Patel | |
| 4 | Kim | Nguyen | Kim Nguyen | |
| 5 | Lee | Brown | Lee Brown | |
| 6 | Sam | Ortiz | Sam Ortiz |
Sample data. First names in A, last names in B, the formula in D2 is copied down.
The formula
Click the first Full name cell (D2) and type:
=A2&" "&B2D2 shows Ana Lopez. Copy it down and every row joins its own names.
- & joins two pieces of text.
- " " is a space in double quotes. Without it you get AnaLopez.
The same result with a function: =CONCATENATE(A2," ",B2) also gives Ana Lopez.
Last name first
Swap the order and put a comma and a space in the middle:
=B2&", "&A2Row 2 shows Lopez, Ana, handy for a list sorted by surname.
A middle name that is sometimes empty
With first, middle and last names in G, H and I, joining all three with & leaves two spaces wherever the middle name is blank ("Ana Lopez"). TEXTJOIN skips the empty cell:
=TEXTJOIN(" ",TRUE,G2,H2,I2)Raj K Patel shows as Raj K Patel, and Ana with no middle name shows as Ana Lopez with one space. The TRUE tells it to ignore empty cells. TEXTJOIN is in Excel 2019 and later, Microsoft 365 and Google Sheets.
Fix the capitals
Names typed as "ana" and "LOPEZ"? PROPER makes the first letter of each word a capital and the rest small:
=PROPER(K2&" "&L2)The result is Ana Lopez. Check names like McDonald by eye afterwards, since PROPER gives Mcdonald.
Extra spaces
Names pasted from a form often carry spaces before or after them. TRIM removes those and turns double spaces into single ones:
=TRIM(M2&" "&N2)" Kim " and "Nguyen " come out as Kim Nguyen.
Initials
LEFT takes the first letter of each name:
=LEFT(A2,1)&LEFT(B2,1)Ana Lopez becomes AL. The & sign, CONCATENATE, PROPER, TRIM and LEFT work in every version of Excel and in Google Sheets.
Templates with the math already done
Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.
- Wedding Guest List SpreadsheetEvery guest, household and plus-one with RSVPs, meals and seating.
- Attendance TrackerA whole school year for up to 40 students with a year summary.
- Teacher GradebookScores in, overall percent, letter grade and missing work out, for up to 40 students.
More how-tos
- Find a price by its code with XLOOKUPType a product code, get its price back from your price list. One formula, no helper columns.
- Total each category with SUMIFSAdd up all the Fuel, all the Office, all the Meals: one formula per category, and more rules when you need them.
- Check two conditions at once with IF and ANDFree shipping only when the order is 50 or more and ships in Canada: one IF, one AND, done.
- Add leading zeros to numbersType 00021 and Excel or Google Sheets shows 21, because to them it is just the number 21. Here is how to get the zeros back, and keep them.
- Fix #DIV/0! in an averageAn average of nothing is a division by zero, so AVERAGE shows #DIV/0! until the first number arrives. One IF fixes it.
- Count rows that meet two conditions with COUNTIFSHow many of Ana's deals were 500 or more? COUNTIFS answers it in one cell, and takes as many rules as you need.
- Calculate percentage changeSales went from 400 to 500. That is a 25% increase, and one short formula works it out for every row.
- Calculate years of service from a start dateAna started on 15 March 2019. How many full years has she worked? DATEDIF counts them, and the same formula works out an age from a date of birth.
- Find duplicates in a list with COUNTIFA mailing list where some people signed up twice. One formula shows how many times each email appears, so the repeats stand out.
- Split a full name into first and last nameYour list has "Ana Lopez" in one cell and you need Ana and Lopez in two. Two short formulas find the space and split there.
- Free pricing calculatorsPrice a job from your real costs and margin. No sign up.