TEXTJOIN と CONCAT：Excel と Google Sheets での使い分け

要点

区切り文字が必要で、空セルを飛ばすかどうかを決めたいときは TEXTJOIN です。範囲は SKU-101、Widget、空白、12.50。TEXTJOIN(",",TRUE,A2:A5) は SKU-101,Widget,12.50、第 2 引数 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 は 4 セルで 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