Updated August 18, 2026. Excel can separate name text, but it cannot reliably decide a person’s true given name, middle name, or family name from spaces alone. For a simple John Smith list, use Text to Columns for a one-time cleanup, Flash Fill for a quick pattern, or TEXTBEFORE/TEXTAFTER for a repeatable Microsoft 365 or Excel 2024 formula. Names with middle names, prefixes, suffixes, particles, or compound surnames need an explicit rule and review.
Keep an untouched copy of the original full-name column before transforming anything.
Choose the method that fits your data
| Situation | Recommended method | Availability | Why |
|---|---|---|---|
One-time, consistently formatted First Last list |
Text to Columns | Desktop Excel; not the Excel for the web wizard | Fast visual workflow |
| Small list with an obvious pattern | Flash Fill | Current desktop Excel; web availability depends on build | Minimal setup |
| Microsoft 365 or Excel 2024, first and final token rule | TEXTBEFORE/TEXTAFTER |
Microsoft 365 and Excel 2024, including supported web builds | Precise dynamic formulas |
| Need every word in separate columns | TEXTSPLIT |
Microsoft 365 and Excel 2024 | Dynamic spill output |
| Excel 2016, 2019, or 2021 compatibility | Legacy formulas or Power Query | Desktop Excel editions that include the feature | Avoids unsupported newer functions |
| Recurring imports or large datasets | Power Query | Microsoft 365, Mac, Excel 2024, 2021, 2019, and 2016 as documented by Microsoft | Refreshable transformation |
| Mixed formats and exceptions | Cleanup formulas plus review flags | All editions with the required functions | Preserves uncertain rows for checking |
Microsoft’s documented approaches and the Excel for the web limitation are described at Microsoft’s split-a-cell guide. Function availability can vary by exact build, update channel, operating system, and license.
First define what “first and last” means
A delimiter-based operation splits characters; it does not perform cultural or legal name recognition.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstall#1 Best Overall
- Fundamental, two-line calculator that combines statistics and advanced scientific functions for high school math and science
- Two-line display shows the entry and calculated result at the same time for easy understanding of the calculation
- Fraction features, conversions, and basic scientific and trigonometric functions
- Solar and battery powered
- Approved for use on SAT, ACT and AP exams
| Input | Possible interpretation |
|---|---|
John Smith |
Given name John; family name Smith |
John Michael Smith |
Given John, middle Michael, family Smith |
Smith, John |
Family Smith; given John |
Mary Ann Johnson |
Mary Ann may be a compound given name, or Mary plus middle name Ann |
Dr. Jane Smith |
Dr. is a prefix, not a given name |
Robert Smith Jr. |
Jr. is a suffix, not a family name |
Ana María de la Cruz |
The family name may contain several words |
Before choosing a formula, decide whether your rule is “first token and last token,” “first token and everything after it,” or “everything before the final token and the final token.” If the source system already supplies separate name fields, use those fields instead of reconstructing them.
Method 1: Text to Columns
Best for a one-time, uniform list
Use this for rows such as John Smith, Maria Garcia, and David Lee when each name has exactly two space-delimited parts.
- Select the full-name column.
- Insert blank columns to the right, or choose a safe destination. Text to Columns can overwrite adjacent cells.
- Choose Data > Data Tools > Text to Columns.
- Select Delimited, then choose Space.
- Check the preview and select Finish or Apply, depending on the interface.
- Rename the output columns First Name and Last Name.
Mary Ann Johnson will become three columns because Excel splits at every selected space. For Smith, John, select comma as the delimiter instead; then trim the space after the comma if necessary.
Method 2: Flash Fill
Best when a few examples clearly show the pattern
- Put First Name in
B1. - Type the intended first-name result for the first source row in
B2. - Begin typing the next result in
B3. When Excel previews the pattern, press Enter. - Alternatively, select the destination range and choose Data > Flash Fill, or press Ctrl+E on Windows.
- Repeat in a separate column for last names.
Flash Fill copies a demonstrated pattern; it is not a name database. Verify rows containing middle names, inconsistent prefixes, suffixes, compound surnames, or different source formats. A later change to the source list does not create a formula-based transformation.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Method 3: TEXTSPLIT
Best for Microsoft 365 and Excel 2024 dynamic arrays
Microsoft documents TEXTSPLIT for Microsoft 365 and Excel 2024, with column and row delimiters, ignored empty values, match mode, and padding options. See Microsoft’s TEXTSPLIT documentation.
Split on spaces
With the full name in A2:
=TEXTSPLIT(TRIM(A2)," ")The result spills across columns. A three-word name produces three outputs, so this separates every token rather than deciding which words are the given and family names.
Rank #2
- View multiple calculations at the same time: Compare results and explore patterns on-screen with the MultiView display that supports up to four lines
- See math exactly as it appears in textbooks: Display math expressions, symbols and stacked fractions exactly the way they appear in textbooks — no need to adapt to a technical syntax; provides quick access to frequently used functions
- Scientific notation output: View scientific notation with the proper superscripted exponents and see the output in scientific notation
- Explore (x,y) table of values: Students can easily explore an (x,y) table of values for a given function automatically or by entering specific x values
- The TI-30XS MultiView scientific calculator is ideal for general math, Pre-Algebra, Algebra 1 and 2, Geometry, Statistics, general science, Biology and Chemistry
Ignore repeated spaces
=TEXTSPLIT(TRIM(A2)," ",,TRUE)The fourth argument ignores empty results caused by consecutive delimiters.
Split comma-formatted names
=TRIM(TEXTSPLIT(A2,","))For Smith, John, the first output is the family name and the second is the given name.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Handle more than one delimiter
=TEXTSPLIT(A2,{",",";"})This treats commas and semicolons as column delimiters. The spill area must be empty or Excel returns #SPILL!.
Method 4: TEXTBEFORE and TEXTAFTER
Best formula when the rule is first token plus final token
These functions are documented for Microsoft 365 and Excel 2024. They are often safer than TEXTSPLIT when middle words should not create extra output columns.
First token
=IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2))Final token
=IFERROR(TEXTAFTER(TRIM(A2)," ",-1),"")For John Michael Smith, these formulas return John and Smith; Michael is intentionally not included in either result. That is a business rule, not a claim that Excel identified the person’s true family name.
Method 5: Legacy formulas
Compatible with older Excel functions
These formulas use broadly supported functions such as LEFT, RIGHT, LEN, SEARCH, and SUBSTITUTE.
Rank #3
- Scientific Calculator with Graphic Function: All-in-one scientific and graphing calculator. Supports plotting functions, analyzing graphs, and solving complex equations. Displays graphs and formulas simultaneously for clear visualization. Ideal for algebra, calculus, and exam prep.
- Compact and Comfortable Design: This scientific and graphing calculator sized at 7 x 3.3 inches for a balanced and ergonomic feel. Fits easily in one hand or on a desk without taking up space. Ideal for long study sessions, test environments, and everyday academic or professional use; smooth button layout supports efficient input and navigation.
- Multiple Modes and 360+ Functions: Includes angle measurement, calculation, and display modes for flexible use across subjects. This scientific and graphing calculator supports over 360 functions such as fractions, complex numbers, statistics, linear regression, standard deviation, and variable solving. Ideal for mastering algebra, geometry, trigonometry, and advanced math applications.
- Durable and Portable Design: Built with an anti-drop body that resists everyday impacts for long-term use. This scientific and graphing calculator is lightweight and slim for easy carrying in a backpack or pocket that includes a protective case to guard the screen and buttons during travel or storage.
- If you cannot turn on the calculator, please press the reset button on the back! If you have any further problems, we offer a limited warranty of 365 days. Please contact us and we will give you an answer within 24 hours.
First token, including one-word rows
=LEFT(TRIM(A2),SEARCH(" ",TRIM(A2)&" ")-1)Final token
=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2))))Exactly two tokens only
=LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(" ",TRIM(A2)))The last pair is appropriate only when every nonblank row contains exactly one space. Do not use it for names with middle words if the desired family name is only the final token.
Method 6: Power Query
Best for repeatable imports and large tables
- Convert the source range to a table with Ctrl+T.
- Select a cell in the table and choose Data > From Table/Range.
- In Power Query Editor, select the name column.
- Choose Home > Split Column > By Delimiter.
- Select a space, comma, or custom delimiter.
- Choose Each occurrence, Left-most delimiter, or Right-most delimiter.
- Rename the output columns.
- Choose Home > Close & Load.
For Mary Ann Johnson under the rule “everything before the final space is the given/middle field,” choose Right-most delimiter. The result keeps Mary Ann together and places Johnson in the last-name field. Power Query also supports splitting by character count, position, case transitions, and digit/non-digit transitions, as described in Microsoft’s Power Query guide.
When new source data arrives, refresh the query. The result is a query output, not a live worksheet formula.
Method 7: Rule-based cleanup and exception handling
Separate first, middle, and last under a stated rule
In Microsoft 365 or Excel 2024, this formula applies the rule “first token = first name, final token = last name, words between them = middle name.”
Free tools Windows power users keep installed
One-click scans. No signup required.
=LET(n,TRIM(A2),first,TEXTBEFORE(n," "),last,TEXTAFTER(n," ",-1),middle,IFERROR(TEXTBEFORE(TEXTAFTER(n," ")," "&last),""),HSTACK(first,middle,last))It needs adjustment for one-word names, suffixes, prefixes, compound surnames, and compound given names. Keep the original value and add a review flag rather than forcing uncertain rows into columns.
Flag likely exceptions
=IF(OR(A2="",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))<1,ISNUMBER(SEARCH(" Jr",A2)),ISNUMBER(SEARCH(" Sr",A2)),ISNUMBER(SEARCH(" III",A2))),"Review","OK")This is a screening rule, not a complete parser. A practical table can contain Original Full Name, First Name, Middle Name, Last Name, Suffix, and Review Status.
Rank #4
- Natural Textbook Display presents formulas and results exactly as written in textbooks for intuitive learning.
Clean the source before splitting
Extra or non-breaking spaces
For ordinary repeated spaces, use TRIM. Data copied from a website or PDF may contain a non-breaking space (character 160), which is not always removed by TRIM:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))Use the cleaned result as the input to your chosen split method.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsPrefixes and suffixes
Dr. Jane Smith returns Dr. as the first token, while Robert Smith Jr. returns Jr. as the final token. Remove or extract prefixes and suffixes into their own fields before applying a first/final-token rule.
Compound surnames, hyphens, and apostrophes
A last-token rule turns Ana de la Cruz into Cruz and Jean van der Berg into Berg, which may be wrong. Maintain a particle/reference list or review these rows. Space-based methods normally preserve Anne-Marie, Smith-Jones, O'Brien, and D'Angelo because those characters are not spaces.
Blank and one-word names
A value such as Madonna has no delimiter-based last name. Do not automatically duplicate it into both columns; leave the family-name field blank or route the row for review.
Common errors and recovery
#SPILL!: Clear cells blocking the dynamic-array output, or select a different destination.- Wrong columns after Text to Columns: Undo, insert blank destination columns, and rerun the wizard with the correct delimiter.
- Unexpected extra columns: A space delimiter splits every occurrence. Use a right-most delimiter rule or first/final-token formulas instead.
- Wrong results from Flash Fill: Undo and provide examples that cover each format, then inspect the filled range manually.
- Formula errors on blanks: Wrap formulas with
IForIFERRORand normalize the input withTRIM. - Excel for the web: Microsoft says the Text to Columns Wizard is unavailable there; use worksheet functions such as
TEXTSPLITwhere supported. Power Query desktop steps are not equivalent to the browser workflow.
Which method should you use?
- Fastest one-time split: Text to Columns for a clean, two-token list.
- Fastest small-list demonstration: Flash Fill, followed by a sample check.
- Best modern formula:
TEXTBEFOREandTEXTAFTERwhen your rule is first token plus final token. - Need every word separated:
TEXTSPLIT. - Older Excel: Legacy formulas or Power Query.
- Monthly or recurring imports: Power Query with a refreshable query.
- Middle names to remain together: Split on the right-most delimiter, or explicitly preserve everything before the final token.
- Mixed or culturally varied names: Preserve the source value, define a review rule, and do not treat a delimiter result as confirmed identity data.
Excel edition notes
TEXTSPLIT, TEXTBEFORE, and TEXTAFTER are not universal across all perpetual desktop editions. Microsoft lists TEXTSPLIT for Microsoft 365 and Excel 2024, while legacy functions work across substantially older versions. Microsoft’s Power Query documentation covers Microsoft 365, Mac, Excel 2024, 2021, 2019, and 2016, subject to edition and build. Check your installed version before distributing a workbook that depends on newer functions.
Best Value
- Natural Textbook Display presents formulas and results exactly as written in textbooks for intuitive learning.
A free Excel for the web account can run supported worksheet functions, but advanced desktop workflows and the Text to Columns Wizard may not be available. See Microsoft’s Microsoft 365 and Office 2024 comparison for product differences.
Frequently Asked Questions
How do I split a full name into two columns in Excel?
For a clean two-word list, use Data > Data Tools > Text to Columns, choose Delimited, select Space, and verify that blank columns are available to the right.
How do I split Last, First names?
Use a comma as the delimiter, then apply TRIM to remove the space after the comma. The first output is the family name and the second is the given name.
How do I keep middle names together?
Use a right-most delimiter in Power Query, or extract the first token and final token with TEXTBEFORE and TEXTAFTER while leaving the middle text in its own field.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Can Excel identify a person’s real surname automatically?
No. Excel can apply a delimiter rule, but it cannot reliably infer compound surnames, cultural naming conventions, or whether a word is a middle name without a defined data rule or reference list.
Why does TEXTSPLIT show #SPILL!?
Cells in the intended spill range are occupied. Clear those cells or move the formula to an empty area.
Which formulas work in Excel 2016?
Use legacy functions such as LEFT, RIGHT, LEN, FIND, SEARCH, SUBSTITUTE, and TRIM, or use Power Query where available.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
