한국어

구분 기호로 텍스트 나누기: Excel과 Google Sheets

Google Sheets에서는 SPLIT으로 구분 기호를 기준으로 오른쪽 칸에 나눕니다. SPLIT("SKU-101,Widget,12.50",",")는 SKU-101, Widget, 12.5입니다. TEXTSPLIT은 이 파일에서 #NAME?이며 Excel 365 함수입니다. INDEX로 두 번째를 가져오면 Widget이고, LEFT와 FIND로 첫 조각을 가져오면 텍스트 SKU-101입니다.

Excel 사용법 작성자 Oscar 읽는 시간 5분 게시일
목차

빠른 답변

Google Sheets에서는 SPLIT으로 구분 기호를 기준으로 오른쪽 칸에 나눕니다. SPLIT("SKU-101,Widget,12.50",",")는 SKU-101, Widget, 12.5입니다. TEXTSPLIT은 이 파일에서 #NAME?이며 Excel 365 함수입니다. INDEX로 두 번째를 가져오면 Widget이고, LEFT와 FIND로 첫 조각을 가져오면 텍스트 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가 되었고, 세 조각 모두 ISNUMBER가 TRUE, 첫 조각의 ISTEXT는 FALSE였습니다. 쉼표 뒤에 공백이 있으면 두 번째는 앞 공백이 있는 Widget이고, TRIM 후에 Widget이 됩니다.

주의점 및 호환성

기본 SPLIT은 a,,c의 빈 칸을 버리고, 네 번째 인수를 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에서 앞자리 0 유지하기
Excel 사용법

Excel과 Google Sheets에서 전체 이름을 이름·중간 이름·성으로 나누기

두 단어 이름은 첫 단어와 마지막 단어를 취하세요. 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분