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。

邊界與相容性

需要帶分隔符連線一整列時，在本測試中應使用 TEXTJOIN，而不是 CONCAT。微軟文件寫明 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