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