日本語

区切り文字でテキストを分割する：Excel と Google Sheets

Google Sheets では SPLIT で区切り文字ごとに右へこぼします。SPLIT("SKU-101,Widget,12.50",",") は SKU-101、Widget、12.5 です。TEXTSPLIT はこのファイルでは #NAME? で、Excel 365 の関数です。INDEX で 2 番目を取ると Widget、LEFT と FIND で 1 番目を取ると文字列の SKU-101 です。

Excel 使い方 著者 Oscar 読了目安 5分 公開日
目次

要点

Google Sheets では SPLIT で区切り文字ごとに右へこぼします。SPLIT("SKU-101,Widget,12.50",",") は SKU-101、Widget、12.5 です。TEXTSPLIT はこのファイルでは #NAME? で、Excel 365 の関数です。INDEX で 2 番目を取ると Widget、LEFT と FIND で 1 番目を取ると文字列の SKU-101 です。

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

確認のポイント

数値に見える部分は数値になります。SPLIT("001,002,12.50",",") は 1、2、12.5 で、3 つとも ISNUMBER が TRUE、最初の ISTEXT は FALSE でした。コンマのあとに空白があると 2 番目は先頭空白付きの Widget で、TRIM すると Widget になります。

注意点と互換性

既定の SPLIT は a,,c の空の段を捨て、第 4 引数を FALSE にすると空の段を残します。セミコロンでも同様に分割できます。区切りがなければ元の文字列が返り、エラーにはなりません。右側のセルが埋まっていると SPLIT は #REF! で、既存値は上書きしません。

英語の詳細例・数式・著者メモ

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.

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

Oscar

表計算の数式とトラブル解決を紹介。検証環境と未確認事項は各記事を参照。

Tested in Google Sheets, November 12, 2026. Excel version notes follow Microsoft documentation and were not re-tested in a desktop Excel workbook on this date..

#split#textsplit#text#delimiters
次の記事：データの整形と入力 Excel と Google Sheets で先頭のゼロを残す
Excel 使い方

Excel と Google Sheets で氏名を名・ミドル・姓に分割する

2語の氏名は先頭の語と末尾の語を取ります。Google Sheets では TRIM のあと LEFT/FIND と末尾語の式で、Ada Lovelace は Ada と Lovelace になりました。この検証では TEXTBEFORE、TEXTAFTER、TEXTSPLIT が #NAME? だったため、どのシートでも使えると思わないでください。

読了目安 9分
Excel 使い方

テキストから数値を取り出す：Excel と Google Sheets

Google Sheets では REGEXEXTRACT で最初の数値を取り、本当の数値にしたいときだけ VALUE を掛けます。パターンの先頭に任意のマイナスを安易に付けないでください。SKU-103 costs $12.50 では -103、INV-2026-11 では -2026 が返りました。セル先頭だけ符号を許す書き方なら、同じ例で 103 と 2026 になり、-3.2C では -3.2 のままです。

読了目安 7分