To create an alphabetized list of distinct names, put the source names in A2:A20 and enter =SORT(UNIQUE(A2:A20)) in C2; Excel returns the names as a dynamic list below the formula. The options below cover other common jobs, from combining names into one cell to filtering a roster or splitting a full name into parts. They are written for Microsoft Excel for Microsoft 365 on Windows and are useful for anyone maintaining a contact list, class roster, staff directory, or similar spreadsheet.
1. Combine a range of names into one cell with TEXTJOIN
With names in A2:A6 and the result in C2, enter =TEXTJOIN(", ",TRUE,A2:A6). If the cells contain Ava Chen, Ben Ortiz, and Dana Wu, the result is Ava Chen, Ben Ortiz, Dana Wu, with a comma and space between each entry. TEXTJOIN is useful for creating a compact label, pasting a short roster into an email, or building a single-cell list for a report; its TRUE argument skips blank cells so an empty row does not create an extra comma.
2. Remove repeated names with UNIQUE
With names in A2:A20 and the output starting in C2, enter =UNIQUE(A2:A20). If Ava Chen appears twice and Ben Ortiz once, the spilled result includes Ava Chen and Ben Ortiz only, in the order their first occurrences appear in the source range. UNIQUE is available in Microsoft 365 and Excel 2021 or later, and is a practical way to turn imported sign-up records into a shorter roster without deleting or changing the original data.
3. Put names in alphabetical order with SORT
With a filled name range in A2:A6 and the result beginning in C2, enter =SORT(A2:A6). For Ava Chen, Dana Wu, and Ben Ortiz in the source, the result spills as Ava Chen, Ben Ortiz, Dana Wu. SORT is worth using when you want the displayed list to update as names change, while leaving the source order untouched; place it beside or on another sheet rather than sorting the original cells if their row order is meaningful.
4. Show only names matching a criterion with FILTER
With names in A2:A20, departments in B2:B20, and the output in D2, enter =FILTER(A2:A20,B2:B20="Sales","No matches"). If the Sales records belong to Ava Chen and Ben Ortiz, those two names spill into D2 and the cells below it, while names from other departments are excluded. FILTER is available in Microsoft 365 and Excel 2021 or later; it is useful for making a live team roster, and the third argument gives a readable message instead of an error when no rows meet the condition.
5. Count text names in a range with COUNTIF
With names or blank cells in A2:A100 and the count formula in C2, enter =COUNTIF(A2:A100,"?*"). If four cells contain names and the remaining cells are blank, the result is 4; the wildcard pattern matches text containing at least one character. This is useful for checking how many entries have been supplied in a partly completed roster, and avoids counting empty cells; it is intended for text names rather than numeric IDs stored as numbers.
6. Clean extra spaces and standardize capitalization
With a name typed in A2 and the cleaned version in B2, enter =PROPER(TRIM(A2)). For a cell containing the text jORDAN lee with extra spaces around and between the words, the result is Jordan Lee. TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces to one, while PROPER capitalizes the first letter of each word; check the results before replacing source data, because it may not preserve special capitalization such as McDonald or names with unusual apostrophe conventions.
7. Extract the first name with TEXTBEFORE
With a full name in A2 and the desired first-name result in B2, enter =IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2)). For Nia Grace Patel, it returns Nia, and for a single-word name such as Cher it returns Cher rather than an error. TEXTBEFORE is available in Microsoft 365 and Excel 2024; the IFERROR wrapper handles names with no space, while TRIM helps prevent leading spaces from creating a misleading first result.
8. Extract the last word as the surname with TEXTAFTER
With the full name in A2 and the result in B2, enter =IFERROR(TEXTAFTER(TRIM(A2)," ",-1),TRIM(A2)). For Nia Grace Patel, the formula returns Patel because the negative instance number searches for the final space; for Cher, the fallback returns Cher. TEXTAFTER requires Microsoft 365 or Excel 2024, and this approach is most suitable when the desired output is the final word, not necessarily a culturally complete family name.
9. Build a full name from separate first- and last-name columns
With first names in A2, last names in B2, and the combined result in C2, enter =TEXTJOIN(" ",TRUE,A2,B2). If A2 contains Ava and B2 contains Chen, the result is Ava Chen; if one of the two cells is blank, TRUE makes TEXTJOIN omit that empty part without leaving a leading or trailing space. This is a dependable option for assembling display names from separate fields, and it can be filled down a table by dragging the fill handle or copying the formula to the rows below.
10. Split a full name across columns with TEXTSPLIT
With a full name in A2 and the formula entered in B2, use =TEXTSPLIT(TRIM(A2)," "). If A2 contains Nia Grace Patel, the formula spills Nia into B2, Grace into C2, and Patel into D2; make sure those destination cells are empty so the spilled result has room. TEXTSPLIT is available in Microsoft 365 and Excel 2024, and is handy when an imported list needs separate word fields, though it treats each space as a separator and does not determine which word is legally or culturally a surname.
How to Choose
Start with the shape of the result you need. Use TEXTJOIN when the goal is one cell containing several names, and use SORT, UNIQUE, or FILTER when you want a visible, expanding list in the worksheet. These dynamic-array formulas spill into neighboring cells, so keep the destination area clear; an existing value in the spill range can produce a #SPILL! error. If you need a fixed list for a one-time report, copying the result and using Paste Special > Values can turn the current output into static text.
Next, check the source data and the rule that defines a name. UNIQUE can remove repeated entries, but it does not decide whether two differently typed versions, such as Ava Chen and Ava Chen, should count as the same person; clean inconsistent spacing first. FILTER is only as reliable as its criteria column, so check for spelling variants such as Sales and sales team. For counting, use COUNTIF when you want to count text entries and are comfortable with the wildcard rule, rather than assuming COUNTA will ignore every formula-generated blank.
Finally, check your Excel version and the intended meaning of each name field. SORT, UNIQUE, and FILTER require Microsoft 365 or Excel 2021 or later; TEXTBEFORE, TEXTAFTER, and TEXTSPLIT require Microsoft 365 or Excel 2024. The formulas work in current Microsoft 365 for Mac and Excel for the web too, though ribbon locations and some interface details can differ; the formulas themselves are the same. If names contain middle names, compound family names, suffixes, or non-space separators, test several representative rows before filling a formula down the whole directory.
Frequently Asked Questions
What is the simplest formula to put several names in one cell?
Use TEXTJOIN. With names in A2:A10, =TEXTJOIN(", ",TRUE,A2:A10) joins them with commas and skips blanks. Change the delimiter in the first argument to a space or line break as needed; a line break can be specified with CHAR(10), and the cell may need Wrap Text enabled to display it on separate lines.
How do I make a list of names without duplicates?
Enter =UNIQUE(A2:A100) in an empty cell, assuming the source list is in A2:A100. It spills distinct entries in their original order. To alphabetize the result as well, wrap UNIQUE in SORT as shown above; both functions require Microsoft 365 or Excel 2021 or later.
Why does my formula show #SPILL!?
A dynamic-array formula such as SORT, UNIQUE, FILTER, or TEXTSPLIT needs empty cells for its results. Clear any values, merged cells, or other obstruction in the expected output area, then check whether the formula can spill. The formula cell itself should also be outside an Excel Table if the formula needs to return a multi-cell array.
Can Excel identify first and last names reliably?
It can split text using a chosen separator, but a formula cannot infer every naming convention. TEXTBEFORE returns the first word, and TEXTAFTER with a negative instance number returns the final word; neither automatically handles every compound surname, suffix, or name written in a different order. Review those cases instead of treating a split as a definitive identity rule.
Will these formulas work in older Excel versions?
TEXTJOIN, COUNTIF, TRIM, and PROPER are available in many older Excel releases, but the dynamic-array functions have version requirements. In particular, SORT, UNIQUE, and FILTER need Microsoft 365 or Excel 2021 or later, while TEXTSPLIT, TEXTBEFORE, and TEXTAFTER need Microsoft 365 or Excel 2024. If a function is not recognized, check the Excel version before changing the formula.
Final Thoughts
Choose the formula that matches the job: TEXTJOIN assembles names, UNIQUE and SORT organize a list, FILTER selects a subset, and the text functions clean or break apart name fields. Keep the original entries intact while checking formula results, especially when a directory includes inconsistent spelling or complex names. Once the output looks right, you can use it as a live list or copy and paste values for a fixed report.
Related
- Excel File Too Large for No Reason? Find the Hidden Data
- Excel File Large for No Reason? Causes and Fixes to Try
- Excel Essential Keyboard Shortcuts for Everyday Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel: Count Dates by Day, Month, or RangeYes. Excel’s COUNTIF function can count dates that match a specific day or a condition such as before…
- Excel FILTER and UNIQUE: Remove Duplicate Rows EasilyExcel’s FILTER function does not remove duplicates by itself; combine it with UNIQUE to return distinct rows that…
- Excel Formula for the First 3 Digits: Text or Numbers?To return the first three characters from a value in cell A2, use =LEFT(A2,3). The result is text,…
- Sort Excel by Color: Steps, Options, and TroubleshootingTo sort an Excel sheet by color in Microsoft 365 for Windows, select a cell in your data,…
- Structured References in Excel Formulas: A Practical GuideStructured references are Excel table references that use a table’s name and column headings instead of ordinary cell…
- COUNTIF Calculation Examples: Text, Dates, and NumbersCOUNTIF counts cells in a range that meet one condition, such as a number, text value, date, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.