按分隔符拆分文本：Excel 與 Google Sheets

Which versions this applies to

The formulas below were run in Google Sheets on November 12, 2026. SPLIT is a Sheets function. Microsoft documents TEXTSPLIT for Excel 365 / Excel 2021; it is not in Excel 2016 or 2019, and it is not in this Google Sheets file (#NAME?). Older Excel can use Text to Columns on the Data tab, which I did not re-test as a UI walkthrough today. For a one-field extract without spilling, LEFT/FIND worked here and is available in both products.

A plain comma split

A2 was the text SKU-101,Widget,12.50:

=SPLIT(A2,",")
=INDEX(SPLIT(A2,","),1,2)
=LEFT(A2,FIND(",",A2)-1)
=TEXTSPLIT(A2,",")

SPLIT spilled SKU-101, Widget, and 12.5 across three cells. INDEX(...,1,2) returned Widget. LEFT/FIND returned SKU-101, the text before the first comma, without a spill. TEXTSPLIT returned #NAME?.

The price did not stay 12.50. It became the number 12.5. That matters as soon as you care about trailing zeros, or about keeping a code that just happens to look numeric.

Leading zeros are stripped too

A9 was the text 001,002,12.50:

=SPLIT(A9,",")

The three spilled values were 1, 2, and 12.5. ISNUMBER was TRUE on all three, and ISTEXT on the first piece was FALSE. 001 is no longer a zero-padded code. If the first field is a SKU or an employee id, convert the result back to text and pad it, or keep the source column and extract with LEFT/MID/FIND, which left SKU-101 as text in the test above. Convert Numbers Stored as Text covers the other direction; this is the same type-coercion in reverse.

Spaces around the delimiter

A3 was SKU-101, Widget, 12.50 (a space after each comma):

=SPLIT(A3,",")
=TRIM(INDEX(SPLIT(A3,","),1,2))

The second piece came out as Widget, with the leading space still there. TRIM of that piece returned Widget. SPLIT does not trim around the delimiter on its own. If your export puts spaces after commas, wrap the piece in TRIM. For the stubborn non-breaking space, see TRIM Not Working.

A space can also be the delimiter. SPLIT("apple banana cherry"," ") spilled apple, banana, and cherry.

Empty tokens and other delimiters

A5 was a,,c (two commas with nothing between them). The default SPLIT dropped the empty piece and returned a and c only. To keep the empty field, pass FALSE as the fourth argument:

=SPLIT(A5,",")                → a | c
=SPLIT(A5,",",TRUE,FALSE)     → a | (empty) | c

The third argument, split_by_each, was TRUE in that second call (the default). I am not using a different third-argument value in this article because I did not need one for a single-character comma.

A semicolon worked the same way as a comma: SPLIT("SKU-101;Widget;12.50",";") spilled SKU-101, Widget, and 12.5. If the delimiter is not in the text at all, SPLIT("only-one-field",",") returned only-one-field in a single cell, not an error.

When SPLIT returns #REF!

SPLIT spills to the right. I put =SPLIT(A15,",") next to two cells that already held OCCUPIED and ALSO, and the formula returned #REF!. Those two cells were not overwritten. Clear the spill area, or move the formula. The broader #REF! cases are in #REF! Error in Excel and Google Sheets.

TEXTSPLIT is not a workaround in this Sheets file; it returned #NAME?, because the function is not here. If you only need the first or second field, LEFT/FIND or INDEX(SPLIT(...),1,n) avoids a three-column spill.

Names with spaces are a special case of this problem: see Split Full Names into First, Middle and Last Name.

Excel and Google Sheets differences

Use SPLIT in Google Sheets. Use TEXTSPLIT in current Excel, according to Microsoft, or Text to Columns if you are in an older Excel and splitting once by hand. I did not run TEXTSPLIT in desktop Excel today. LEFT/FIND is the overlap that I did run in Sheets and that exists in both products.

Common mistakes

• Trusting SPLIT to keep 001 or 12.50 as text. In this file those became 1 and 12.5, and ISNUMBER was TRUE.
• Forgetting that spaces after commas stay on the next piece. Widget needed TRIM.
• Leaving values in the spill cells. That was #REF!, not a partial split.
• Pasting an Excel TEXTSPLIT formula into Google Sheets. It returned #NAME? here.
• Assuming a missing delimiter is an error. SPLIT of a string with no comma returned the original string.

FAQ

How do I split a comma-separated cell into columns in Google Sheets?

=SPLIT(A2,",") spilled three columns from SKU-101,Widget,12.50. Give it empty cells to the right.

Why did 001 become 1 after SPLIT?

Sheets coerced that piece to a number. ISNUMBER was TRUE and ISTEXT was FALSE on the first piece of 001,002,12.50. The same run turned 12.50 into 12.5.

How do I keep an empty field between two commas?

=SPLIT(A5,",",TRUE,FALSE) on a,,c returned three pieces, with an empty cell in the middle. The default call dropped the empty field.

Does TEXTSPLIT work in Google Sheets?

Not in this file. TEXTSPLIT(A2,",") returned #NAME?. Use SPLIT, or LEFT/FIND for a single field.

What if I only need the first field, not a spill?

=LEFT(A2,FIND(",",A2)-1) returned SKU-101 from the comma-separated SKU line, as text.

Related tutorials

• Split Full Names into First, Middle and Last Name in Excel & Google Sheets
• Extract Numbers from Text in Excel and Google Sheets
• TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160)
• Convert Numbers Stored as Text in Excel and Google Sheets
• #REF! Error in Excel and Google Sheets: Causes and Fixes

Live-test findings

Google Sheets, November 12, 2026. SPLIT("SKU-101,Widget,12.50",",") spilled SKU-101, Widget, 12.5. INDEX of the second piece was Widget. LEFT/FIND returned SKU-101. TEXTSPLIT was #NAME?. SPLIT("001,002,12.50",",") spilled 1, 2, 12.5, all ISNUMBER TRUE, first piece ISTEXT FALSE. A comma-space input left a leading space on Widget; TRIM removed it. Default SPLIT of a,,c dropped the empty field; SPLIT(...,TRUE,FALSE) kept it. Semicolon split matched the comma split. No delimiter returned the original string. SPLIT into occupied cells was #REF!.

Sources

• Microsoft Support: TEXTSPLIT function: https://support.microsoft.com/en-us/excel/functions/textsplit-function
• Microsoft Support: LEFT function: https://support.microsoft.com/en-us/office/left-function-913824b4-fc42-4c8c-9229-0eaa57dcdf49
• Google Docs Editors Help: SPLIT: https://support.google.com/docs/answer/3094136