TEXTJOIN과 CONCAT: Excel과 Google Sheets에서 어떤 것을 쓸까

빠른 답변

구분 기호가 필요하고 빈 칸을 건너뛸지 정하려면 TEXTJOIN을 쓰세요. 범위는 SKU-101, Widget, 빈 칸, 12.50입니다. TEXTJOIN(",",TRUE,A2:A5)는 SKU-101,Widget,12.50이고, 두 번째 인수가 FALSE이면 SKU-101,Widget,,12.50으로 빈 자리가 남습니다. 공백 구분이고 빈 칸을 무시하면 SKU-101 Widget 12.50입니다.

=TEXTJOIN(",",TRUE,A2:A5)     → SKU-101,Widget,12.50
=TEXTJOIN(",",FALSE,A2:A5)    → SKU-101,Widget,,12.50
=TEXTJOIN(" ",TRUE,A2:A5)     → SKU-101 Widget 12.50

확인 사항

CONCAT(A2,A3)는 SKU-101Widget이고 구분 기호가 없습니다. CONCATENATE는 네 칸에서 SKU-101Widget12.50이 되어 빈 칸이 붙습니다. 앰퍼샌드로 쉼표를 넣으면 SKU-101,Widget,,12.50으로 TEXTJOIN의 FALSE와 같습니다. CONCAT(A2:A5)와 CONCAT(A2,A3,A4,A5)는 이 파일에서 모두 #N/A였습니다.

주의점 및 호환성

범위에 구분 기호를 넣어 이으려면 이번 테스트에서는 CONCAT이 아니라 TEXTJOIN을 쓰세요. Microsoft 문서에는 Excel CONCAT이 범위를 받을 수 있다고 되어 있지만, 이번엔 데스크톱 Excel에서 그 쓰기를 다시 시험하지 않았습니다.

영어 상세 예제, 수식 및 작성자 메모

Which versions this applies to

The results below were measured in Google Sheets on November 12, 2026. Microsoft documents TEXTJOIN, CONCAT, and CONCATENATE for current Excel; TEXTJOIN and CONCAT are not in very old Excel (2016 has TEXTJOIN in some builds with an add-in story I am not going to unpack — I did not test Excel). I did not re-run these cells in desktop Excel today.

TEXTJOIN can skip or keep blanks

=TEXTJOIN(",",TRUE,A2:A5)     → SKU-101,Widget,12.50
=TEXTJOIN(",",FALSE,A2:A5)    → SKU-101,Widget,,12.50
=TEXTJOIN(" ",TRUE,A2:A5)     → SKU-101 Widget 12.50

The second argument is “ignore empty.” TRUE dropped the blank cell so you do not get a doubled comma. FALSE kept a hole for that cell, which is what you want if the position of the field matters. Changing the first argument to a space produced a readable phrase and still skipped the blank.

CONCAT and CONCATENATE do not add delimiters

=CONCAT(A2,A3)                    → SKU-101Widget
=CONCATENATE(A2,A3,A4,A5)         → SKU-101Widget12.50
=A2&","&A3&","&A4&","&A5          → SKU-101,Widget,,12.50
=CONCAT(A2,A3,A4,A5)              → #N/A
=CONCAT(A2:A5)                    → #N/A

CONCAT of two occupied cells worked, with the values stuck together. CONCATENATE of all four cells also stuck them together and treated the blank as nothing, so the result was SKU-101Widget12.50 — no doubled comma, and no delimiter either. The ampersand formula kept the empty field, the same as TEXTJOIN with FALSE.

CONCAT with a range, or with all four arguments including the blank, returned #N/A in this file. I am reporting that result; I am not going to invent a rule about argument counts beyond what I ran. If you need a range plus a delimiter, TEXTJOIN is the function that actually did it.

When to use which

A list with a comma or space, and blanks you may want to skip: TEXTJOIN.
A few cells and no delimiter: CONCATENATE, or CONCAT of two cells, or &.
A range I already have in A2:A5: TEXTJOIN, not CONCAT, in this file.

The opposite direction — splitting a joined string back into columns — is Split Text by Delimiter.

Excel and Google Sheets differences

Microsoft documents CONCAT as accepting a range in Excel. In this Google Sheets file, CONCAT(A2:A5) was #N/A. I did not re-test the Excel range form today, so I am not quoting an Excel result. TEXTJOIN is the safer range-plus-delimiter choice in the file I actually ran.

Common mistakes

Using CONCAT or & and expecting commas to appear. They do not, unless you type them.
Using TEXTJOIN with TRUE when a missing field should stay visible. FALSE kept the extra comma.
Passing a range to CONCAT in this Sheets file and treating #N/A as a missing key. The values were in the cells; TEXTJOIN and CONCATENATE both saw them.
Forgetting that CONCATENATE will also skip the look of a blank (it does not insert a delimiter, so the next value sits right against the previous one).

FAQ

How do I join a column with commas and skip blank cells?

=TEXTJOIN(",",TRUE,A2:A5) returned SKU-101,Widget,12.50 from a range that included a blank.

How do I keep a blank field in the joined text?

=TEXTJOIN(",",FALSE,A2:A5) returned SKU-101,Widget,,12.50. The ampersand formula with explicit commas did the same.

Why did CONCAT return #N/A?

CONCAT(A2,A3) worked. CONCAT(A2:A5) and CONCAT(A2,A3,A4,A5) returned #N/A in this file. I used TEXTJOIN or CONCATENATE instead.

What is the difference between CONCAT and CONCATENATE?

In this test, CONCATENATE of the four cells returned SKU-101Widget12.50. CONCAT of the same four, or of the range, returned #N/A. CONCAT of two occupied cells returned SKU-101Widget. I would not treat them as interchangeable here.

Will TEXTJOIN work in Excel?

Microsoft documents TEXTJOIN for current Excel. I did not re-run these cells in desktop Excel today.

Live-test findings

Google Sheets, November 12, 2026. Range: SKU-101, Widget, blank, 12.50. TEXTJOIN(",",TRUE,...) → SKU-101,Widget,12.50. TEXTJOIN(",",FALSE,...) → SKU-101,Widget,,12.50. TEXTJOIN(" ",TRUE,...) → SKU-101 Widget 12.50. CONCAT(A2,A3) → SKU-101Widget. CONCATENATE of four → SKU-101Widget12.50. Ampersand with commas → SKU-101,Widget,,12.50. CONCAT of four arguments or of A2:A5 → #N/A.

Sources

Microsoft Support: TEXTJOIN function: https://support.microsoft.com/en-us/excel/functions/textjoin-function
Microsoft Support: CONCAT function: https://support.microsoft.com/en-us/excel/functions/concat-function
Microsoft Support: CONCATENATE function: https://support.microsoft.com/en-us/excel/functions/concatenate-function
Google Docs Editors Help: TEXTJOIN: https://support.google.com/docs/answer/7013992
Google Docs Editors Help: CONCATENATE: https://support.google.com/docs/answer/3094123