在 Excel 與 Google Sheets 中把全名拆成名、中間名和姓

Tested in: Live formulas in Google Sheets on November 12, 2026, using the name list below. Excel 365 function behavior is taken from Microsoft’s TEXTBEFORE, TEXTAFTER and TEXTSPLIT documentation and was not re-tested in a desktop Excel workbook on this date.

Which versions this applies to

• Google Sheets: LEFT/FIND, the last-word SUBSTITUTE trick, SPLIT, and REGEXEXTRACT all worked in this test. TEXTBEFORE, TEXTAFTER and TEXTSPLIT returned #NAME?. If your domain already offers those three names in autocomplete, you can use the Excel formulas as written; do not assume they exist until you check.
• Excel for Microsoft 365, Excel 2021 and Excel 2024: Microsoft documents TEXTBEFORE, TEXTAFTER and TEXTSPLIT. The older LEFT/FIND and last-word formulas also work.
• Excel 2019 and earlier: There is no TEXTBEFORE / TEXTSPLIT. Use the LEFT/FIND and last-word formulas, or Data > Text to Columns.

The sample names

First name and last name in Google Sheets

TRIM the cell first. Extra spaces turned Ada Lovelace (LEN 18) into Ada Lovelace (LEN 12), after which the same formulas as the clean row worked.

First word, with a fallback so a single name such as Madonna does not become #VALUE!:

=IFERROR(LEFT(TRIM(A2), FIND(" ", TRIM(A2))-1), TRIM(A2))

Last word, by padding spaces and taking the rightmost 100 characters, then trimming:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), 100))

On Madonna, FIND there is no space, so the first-name formula returned Madonna and the last-word formula also returned Madonna. On Ada Lovelace, they returned Ada and Lovelace. On Mary Ann Evans they returned Mary and Evans, which is usually what you want if the middle name belongs in its own column.

REGEXEXTRACT produced the same first and last tokens in this test:

=IFERROR(REGEXEXTRACT(TRIM(A2), "^(\S+)"), "")
=IFERROR(REGEXEXTRACT(TRIM(A2), "(\S+)$"), "")

Jean-Luc and Billy-Bob stayed as one token, because a hyphen is not whitespace. That is typically what you want.

Middle name (or everything between the first and last word)

This Sheets formula returned Ann for Mary Ann Evans, Jane A. Smith for Dr. Jane A. Smith Jr., de la for Anna-Maria de la Cruz, and a blank for two-word names:

=IFERROR(TRIM(REGEXEXTRACT(TRIM(A2), "^\S+\s+(.+)\s+\S+")), "")

A blank middle name is the correct result for Ada Lovelace. It is not a reliable “middle name” field for the doctor/Jr. row or for particles such as de la, van or von. If you need those handled, you need a rule list or a manual pass, not a longer regex.

Comma-style names: Smith, John

A space split is the wrong tool here. SPLIT on Smith, John produced Smith, and John — the comma stuck to the family name. These two formulas treat a comma as “family name first, given name after” and otherwise fall back to first word / last word:

=IF(A2="", "", IF(ISNUMBER(FIND(",", A2)),
    TRIM(MID(A2, FIND(",", A2)+1, 99)),
    IFERROR(LEFT(TRIM(A2), FIND(" ", TRIM(A2))-1), TRIM(A2))))

=IF(A2="", "", IF(ISNUMBER(FIND(",", A2)),
    TRIM(LEFT(A2, FIND(",", A2)-1)),
    TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), 100))))

In this test they returned John / Smith for Smith, John and Wei-Ming / CHEN for CHEN, Wei-Ming. Rows without a comma, including Ada Lovelace and José García, still used the first-word / last-word path.

If a list mixes both styles, run this pair rather than a single FIND-on-space formula down the column.

Splitting every word into its own column

Google Sheets’ SPLIT function spilled tokens into neighboring cells. For Mary Ann Evans the three cells were Mary, Ann, Evans. For Dr. Jane A. Smith Jr. they were Dr., Jane, A., Smith, Jr.

=SPLIT(TRIM(A2), " ", TRUE, TRUE)

The last two arguments drop extra delimiters, which is why the padded Ada Lovelace row still spilled as two cells, Ada and Lovelace. Leave empty columns to the right. If the next column already has a formula, Sheets returns #REF! (array result was not expanded) instead of the split values — the same class of collision described in Fix “Array Result Was Not Expanded Because It Would Overwrite Data”.

You can also use Data > Split text to columns on a selected range when this is a one-off cleanup rather than a formula you want to keep.

Excel 365: TEXTBEFORE, TEXTAFTER and TEXTSPLIT

Microsoft documents these three functions for Excel for Microsoft 365 (and they are the cleanest way to write the same logic there). They were not available in the Google Sheets file tested on November 12, 2026 (#NAME? on each).

First word and last word:

=IFERROR(TEXTBEFORE(TRIM(A2), " "), TRIM(A2))
=IFERROR(TEXTAFTER(TRIM(A2), " ", -1), TRIM(A2))

The -1 as instance_num means “the last delimiter,” so Mary Ann Evans should return Evans rather than Ann Evans. TEXTSPLIT puts each word in its own cell, like Sheets’ SPLIT:

=TEXTSPLIT(TRIM(A2), " ")

Comma-style in Excel 365 can use the same IF/FIND wrapper, with TEXTBEFORE / TEXTAFTER in place of LEFT / MID:

=IF(ISNUMBER(FIND(",", A2)), TRIM(TEXTAFTER(A2, ",")), IFERROR(TEXTBEFORE(TRIM(A2), " "), TRIM(A2)))
=IF(ISNUMBER(FIND(",", A2)), TRIM(TEXTBEFORE(A2, ",")), IFERROR(TEXTAFTER(TRIM(A2), " ", -1), TRIM(A2)))

Because those Excel formulas were not entered in a desktop workbook this date, treat them as documented usage, then confirm once in your own file. The LEFT/FIND and last-word formulas above are the ones that were actually checked, and they work in both apps.

What a formula cannot sort out for you

These are name-list problems, not formula bugs:

• Titles and suffixes. Dr. Jane A. Smith Jr. will not become Jane / Smith unless you first strip a known list of titles (Dr., Mr., Ms., Prof.) and suffixes (Jr., Sr., III). A first/last-word split of that row was Dr. and Jr.
• Particles and multi-word family names. Anna-Maria de la Cruz gave first word Anna-Maria, last word Cruz, and “middle” de la. Whether the family name should be de la Cruz is a convention, not something FIND can decide.
• Hyphens vs spaces. Jean-Luc and Billy-Bob stayed together, which is usually right. A last name written with a hyphen also stays together.
• Mixed punctuation. If some rows use commas and others do not, detect the comma first, as in the pair of formulas above.
• Hidden characters. If FIND cannot see a space you can see, check for non-breaking spaces the way the TRIM not working article describes, then TRIM again.

Keep the original full-name column. Split results are easy to correct when the source is still there.

Excel vs. Google Sheets

Common mistakes

1. Taking everything after the first space as the last name. For Mary Ann Evans that returns Ann Evans, not Evans. Use the last-word formula or TEXTAFTER with -1.
2. Running a space split on Smith, John. You will keep the comma and reverse the fields. Detect the comma first.
3. Filling an entire column from one happy example. Ada Lovelace is the easy case. The Dr./Jr. row is the one that shows whether the method is good enough for your list.
4. Putting SPLIT or TEXTSPLIT next to other formulas. The spill needs empty cells. This test produced #REF! when SPLIT collided with a neighboring formula column.
5. Skipping TRIM. Leading spaces and doubled inner spaces change FIND positions and inflate LEN. The padded Ada row was 18 characters before TRIM and 12 after.

FAQ

How do I split first and last name in Google Sheets?

Use the LEFT/FIND formula for the first word and the SUBSTITUTE last-word formula for the last word, after TRIM. REGEXEXTRACT with ^(\S+) and (\S+)$ matched the same tokens in this test. Do not assume TEXTBEFORE exists; it returned #NAME? here.

How do I get the last word, not everything after the first space?

=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), 100)) returned Evans for Mary Ann Evans, Cruz for Anna-Maria de la Cruz, and Lovelace for Ada Lovelace.

What about names written as Last, First?

If the cell contains a comma, take the text after it as the given name and the text before it as the family name. In this test, Smith, John became John / Smith and CHEN, Wei-Ming became Wei-Ming / CHEN.

Why did Madonna not return an error?

The first-name formula uses IFERROR, so a cell with no space returns the whole trimmed name. The last-word formula also returns that single word. That is the useful behavior for mononyms.

Will this work for Dr. Jane A. Smith Jr.?

Not as a first-and-last-name split. First word was Dr. and last word was Jr. Strip titles and suffixes first, or correct those rows by hand.

Related tutorials

• TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160)
• Extract Email Addresses from Text in Google Sheets with REGEXEXTRACT
• Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods)
• Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets

Live-test findings

All Sheets results above come from one file dated November 12, 2026. TEXTBEFORE, TEXTAFTER and TEXTSPLIT returned #NAME?. LEFT/FIND, the last-word SUBSTITUTE formula, REGEXEXTRACT, SPLIT, and the comma-aware pair were checked against the twelve sample names, including a padded Ada Lovelace row (LEN 18 before TRIM, 12 after) and accented letters in José García.

Sources

• Microsoft Support: TEXTBEFORE function: https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-acb4-a2aaa278d6aa
• Microsoft Support: TEXTAFTER function: https://support.microsoft.com/en-us/office/textafter-function-c8eb6d1c-5e75-4c02-b734-2d206e726da4
• Microsoft Support: TEXTSPLIT function: https://support.microsoft.com/en-us/office/textsplit-function-b435ed69-1544-433d-86b6-8a0e2c0a1c6a
• Microsoft Support: TRIM function: https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9
• Google Docs Editors Help: SPLIT, LEFT, REGEXEXTRACT, TRIM: https://support.google.com/docs/answer/3094136, https://support.google.com/docs/answer/3094079, https://support.google.com/docs/answer/3098244, https://support.google.com/docs/answer/3094140