ブログ

SUBSTITUTEは、文字列の中の特定の文字を、別の文字に置き換える関数です。

「ハイフンを削除する」「全角スペースを半角に統一する」といったデータ整形で頻繁に使います。ExcelとGoogleスプレッドシートで共通です。

書式

=SUBSTITUTE(文字列, 検索文字, 置換文字, [何番目])

引数	内容
文字列	対象
検索文字	置き換えたい文字
置換文字	置き換え後の文字
何番目	省略するとすべて置き換える

基本の使い方

A1に 03-1234-5678 が入っているとします。

=SUBSTITUTE(A1, "-", "")   → 0312345678

置換文字を ""（空文字）にすると、削除になります。これが最もよく使う形です。

特定の1つだけを置き換える

第4引数で指定します。

=SUBSTITUTE(A1, "-", "／", 2)   → 03-1234／5678

2つ目の「-」だけが変わります。すべてではなく1箇所だけ変えたいときに使います。

REPLACEとの違い

似た関数に REPLACE があります。

	SUBSTITUTE	REPLACE
指定方法	文字で指定	位置で指定
使う場面	特定の文字を置換	何文字目から何文字を置換

=SUBSTITUTE(A1, "-", "")     文字「-」を消す
=REPLACE(A1, 3, 1, "")       3文字目の1文字を消す

位置が固定なら REPLACE、文字で判断するなら SUBSTITUTE です。実務ではSUBSTITUTEのほうが圧倒的に使用頻度が高くなります。

データ整形の定番パターン

不要な文字をまとめて削除する

SUBSTITUTEを入れ子にします。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""), " ", ""), "　", "")

ハイフン、半角スペース、全角スペースを全部削除しています。入れ子は内側から実行されるので、順番を意識してください。

数が多い場合は REGEXREPLACE のほうが短く書けます。

=REGEXREPLACE(A1, "[- 　]", "")

全角を半角に統一する

数字や英字なら ASC が使えます。

=ASC(A1)

特定の文字だけならSUBSTITUTEです。

=SUBSTITUTE(A1, "　", " ")

改行を削除する

=SUBSTITUTE(A1, CHAR(10), "")

CSVやWebからコピーしたデータには、見えない改行が入っていることがあります。LEN() で文字数を確認して、見た目と合わなければこれを疑ってください。

単位を外して数値にする

=VALUE(SUBSTITUTE(A1, "円", ""))

1,234円 のようにカンマも入っているなら、両方外します。

=VALUE(SUBSTITUTE(SUBSTITUTE(A1, "円", ""), ",", ""))

出現回数を数える裏技

SUBSTITUTEの応用でよく使われる書き方です。

=LEN(A1) - LEN(SUBSTITUTE(A1, "-", ""))

「元の長さ」から「その文字を全部消した長さ」を引くと、その文字の個数になります。

複数文字の場合は、文字数で割ります。

=(LEN(A1) - LEN(SUBSTITUTE(A1, "東京", ""))) / LEN("東京")

「東京」が何回出てくるかを数えられます。

最後の区切りより後ろを取り出す

SUBSTITUTEの第4引数を使った定番テクニックです。

=RIGHT(A1, LEN(A1) - FIND("|", SUBSTITUTE(A1, "/", "|", LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

「最後の / だけを | に置き換えて、その位置を探す」という仕組みです。

読みにくいので、実務では REGEXEXTRACT を推奨します。

=REGEXEXTRACT(A1, "([^/]+)$")

全行に適用する

=ARRAYFORMULA(IF(A2:A="", "", SUBSTITUTE(A2:A, "-", "")))

注意点

大文字小文字を区別します。

=SUBSTITUTE("ABC abc", "a", "X")   → ABC Xbc

区別せずに置換したいなら REGEXREPLACE を使ってください。

=REGEXREPLACE(A1, "(?i)a", "X")

結果は文字列になります。 数値として使うなら VALUE で戻してください。

Excelとの違い

SUBSTITUTE・REPLACE ともに共通です。書式も挙動も同じです。

違いは正規表現系の関数です。

	Excel	スプレッドシート
REGEXREPLACE	無い（365の一部を除く）	ある
REGEXEXTRACT	無い	ある

Excelでは複雑な置換にSUBSTITUTEの入れ子を使うしかありませんが、スプレッドシートなら正規表現が使えます。置換対象が3つを超えたら、正規表現に切り替えるのが実用的です。

まとめ

• – 書式は =SUBSTITUTE(文字列, 検索文字, 置換文字)
• – 置換文字を "" にすると削除になる
• – 第4引数で何番目だけを置き換えられる
• – 位置で指定するなら REPLACE、文字で指定するならSUBSTITUTE
• – LEN(A1)-LEN(SUBSTITUTE(...)) で出現回数が数えられる
• – 大文字小文字を区別する。区別しないなら REGEXREPLACE

関連する関数

• – REGEXEXTRACT — 正規表現で取り出す
• – TEXT — 書式を整える
• – SPLIT — 区切り文字で分割する
• – TEXTJOIN — 文字列を連結する
• – 関数一覧に戻る

MIDは、文字列の指定した位置から、指定した文字数だけ取り出す関数です。

LEFT が先頭、RIGHT が末尾なのに対し、MIDは途中の任意の位置を切り出せます。ExcelとGoogleスプレッドシートで共通です。

書式

=MID(文字列, 開始位置, 文字数)

引数	内容
文字列	対象
開始位置	先頭を1として何文字目から
文字数	取り出す長さ

開始位置は1から数えます。 0からではありません。

基本の使い方

A1に ABC-1234-XY が入っているとします。

=MID(A1, 5, 4)   → 1234

5文字目から4文字を取り出しています。

区切り文字の間を取り出す

実務ではこちらが主になります。FIND で位置を求めて計算します。

ABC-1234-XY から真ん中の 1234 を取り出す場合：

=MID(A1, FIND("-", A1) + 1, FIND("-", A1, FIND("-", A1) + 1) - FIND("-", A1) - 1)

読みにくいので分解します。

• – FIND("-", A1) … 1つ目の「-」の位置（4）
• – FIND("-", A1, 5) … 5文字目以降で「-」を探す＝2つ目の位置（9）
• – 開始位置 = 4 + 1 = 5
• – 文字数 = 9 − 4 − 1 = 4

FIND の第3引数（検索開始位置）を使うのがポイントです。

とはいえ、この用途なら SPLIT か REGEXEXTRACT のほうが圧倒的に短く書けます。

=INDEX(SPLIT(A1, "-"), 1, 2)
=REGEXEXTRACT(A1, "-([^-]+)-")

MIDを長い数式で使い始めたら、他の関数への置き換えを検討してください。

文字数を大きめに指定してもいい

文字数が足りなくても、MIDはエラーになりません。残り全部を返します。

=MID(A1, 5, 999)   → 1234-XY

「ここから最後まで」を取り出したいときに便利です。RIGHT で LEN を計算するより簡単です。

実用例

生年月日の文字列から年月日を分ける

A1が 20260401 の場合：

=MID(A1, 1, 4)   → 2026
=MID(A1, 5, 2)   → 04
=MID(A1, 7, 2)   → 01

日付に変換するなら DATE と組み合わせます。

=DATE(MID(A1,1,4), MID(A1,5,2), MID(A1,7,2))

8桁の数値を日付に直す定番の書き方です。ただしA1が数値の場合は TEXT で文字列にしてから渡してください。

=DATE(MID(TEXT(A1,"00000000"),1,4), MID(TEXT(A1,"00000000"),5,2), MID(TEXT(A1,"00000000"),7,2))

商品コードの中間を取り出す

位置が固定なら、MIDが最も素直です。

=MID(A1, 4, 3)

1文字ずつ分解する

ARRAYFORMULA と ROW を組み合わせます。

=ARRAYFORMULA(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1))

A1の文字が1文字ずつ縦に並びます。文字種の判定や、1文字ずつの変換をしたいときに使います。

全行に適用する

=ARRAYFORMULA(IF(A2:A="", "", MID(A2:A, 5, 4)))

位置が固定なら、この形が最も確実です。

エラーの対処

#VALUE! が出る

• – 開始位置に0以下を指定している（1から数える）
• – 文字数にマイナスを指定している

FIND の計算結果がマイナスになっていないか確認してください。

空文字が返る

開始位置が文字列の長さを超えています。エラーにはならず、空文字になります。気づきにくいので注意してください。

=IF(LEN(A1) < 5, "短すぎ", MID(A1, 5, 4))

長さを先に確認する書き方が安全です。

想定と違う位置で切れる

見えない文字（改行・全角スペース・タブ）が含まれています。LEN() で文字数を確認してください。見た目と合わなければ TRIM や CLEAN で整えます。

使い分け

やりたいこと	使う関数
先頭から	LEFT
末尾から	RIGHT
位置が固定の途中	MID
区切り文字で分ける	SPLIT
パターンで取り出す	REGEXEXTRACT

MIDが最も向いているのは、位置が完全に固定されているデータです。固定長のコード、8桁の日付、伝票番号など。可変長になったら他の関数に切り替えてください。

まとめ

• – 書式は =MID(文字列, 開始位置, 文字数)
• – 開始位置は1から数える
• – 文字数を大きめに指定すると残り全部が返る
• – 位置が固定のデータに最適。可変長なら SPLIT / REGEXEXTRACT
• – 開始位置が長さを超えると、エラーにならず空文字が返る
• – 8桁数値を日付にするなら DATE と組み合わせる

関連する関数

• – LEFT — 先頭から取り出す
• – RIGHT — 末尾から取り出す
• – SPLIT — 区切り文字で分割する
• – REGEXEXTRACT — 正規表現で取り出す
• – 関数一覧に戻る

RIGHTは、文字列の末尾から、指定した文字数だけ取り出す関数です。

LEFT の逆向きで、「電話番号の下4桁」「ファイルの拡張子」といった切り出しに使います。ExcelとGoogleスプレッドシートで共通です。

書式

=RIGHT(文字列, [文字数])

文字数を省略すると1文字だけ返します。

基本の使い方

A1に ABC-1234 が入っているとします。

=RIGHT(A1, 4)   → 1234

末尾から可変長で取り出す

末尾の取り出しは、文字数が固定でないことが多いのがLEFTとの違いです。

RIGHT は「末尾から何文字」しか指定できないため、全体の長さから、前半の長さを引くという計算が必要になります。

A1が ABC-1234 の場合、「-」より後ろを取り出すには次のように書きます。

=RIGHT(A1, LEN(A1) - FIND("-", A1))

• – LEN(A1) … 全体の文字数（8）
• – FIND("-", A1) … 「-」の位置（4）
• – 8 − 4 = 4文字を末尾から取る → 1234

この形が実務での基本形になります。

区切りが無い場合に備えて包んでおくと安全です。

=IFERROR(RIGHT(A1, LEN(A1) - FIND("-", A1)), A1)

区切り文字が複数あるとき

FIND は最初に見つかった位置を返します。ファイルパスのように区切りが複数あると、期待通りになりません。

A1 = data/2026/report.csv

最後の区切りより後ろを取り出すには、区切り文字を一度別の文字に置き換える手法を使います。

=RIGHT(A1, LEN(A1) - FIND("|", SUBSTITUTE(A1, "/", "|", LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

読みにくいので、REGEXEXTRACT を使うほうが実用的です。

=REGEXEXTRACT(A1, "([^/]+)$")

「最後の / より後ろ」という意味です。圧倒的に短く、意図も明確です。

実用例

拡張子を取り出す

=REGEXEXTRACT(A1, "\.([^.]+)$")

RIGHT で書くこともできますが、拡張子の長さが可変なので正規表現のほうが確実です。

電話番号の下4桁

=RIGHT(A1, 4)

これは固定長なので RIGHT が最適です。

郵便番号の下4桁

=RIGHT(A1, 4)

ハイフンが入っている場合（123-4567）もそのまま動きます。

括弧の中身を取り出す

=REGEXEXTRACT(A1, "\((.+)\)")

全角・半角の数え方

RIGHTは全角も半角も1文字として数えます。 バイト単位なら RIGHTB ですが、実務で必要な場面は限られます。

日付・数値に使うとき

日付にRIGHTを使うと、内部のシリアル値の末尾が返ります。日付から日を取り出したいなら DAY か TEXT を使ってください。

=DAY(A1)
=TEXT(A1, "d")

数値に使った場合も、結果は文字列になります。計算に使うなら VALUE で戻してください。

ゼロ埋めが必要な数値（0042 のような伝票番号）は、RIGHTで取り出すと先頭の0が消えていることがあります。元データが数値なら TEXT でゼロ埋めしてから取り出してください。

=RIGHT(TEXT(A1, "000000"), 4)

全行に適用する

=ARRAYFORMULA(IF(A2:A="", "", RIGHT(A2:A, 4)))

可変長の場合も同様に包めます。

=ARRAYFORMULA(IF(A2:A="", "", RIGHT(A2:A, LEN(A2:A) - FIND("-", A2:A))))

エラーの対処

#VALUE! が出る

• – FIND が対象を見つけられていない
• – 文字数にマイナスを指定している

IFERROR で包むか、FIND 単体の結果を確認してください。

想定と違う位置で切れる

末尾に見えない文字（改行・スペース）が付いている可能性があります。LEN() で確認し、TRIM で整えてください。

=RIGHT(TRIM(A1), 4)

CSVから読み込んだデータでは、末尾の空白や改行が残っていることがよくあります。

まとめ

• – 書式は =RIGHT(文字列, 文字数)
• – 可変長なら LEN(A1) - FIND(...) で文字数を計算する
• – 区切り文字が複数あるなら REGEXEXTRACT が圧倒的に短い
• – 全角も半角も1文字として数える
• – 末尾の見えない空白に注意。TRIM で整える
• – ゼロ埋めが要る数値は TEXT を先に通す

関連する関数

• – LEFT — 先頭から取り出す
• – MID — 途中から取り出す
• – REGEXEXTRACT — 正規表現で取り出す
• – SPLIT — 区切り文字で分割する
• – 関数一覧に戻る

LEFTは、文字列の先頭から、指定した文字数だけ取り出す関数です。

「商品コードの先頭3文字が分類コード」といった、位置が決まっているデータの切り出しに使います。ExcelとGoogleスプレッドシートで共通です。

書式

=LEFT(文字列, [文字数])

文字数を省略すると1文字だけ返します。

基本の使い方

A1に ABC-1234 が入っているとします。

=LEFT(A1, 3)   → ABC

文字数が決まっていないとき

実務ではこちらのほうが多くなります。区切り文字の位置を FIND で求めて、その手前までを取り出します。

=LEFT(A1, FIND("-", A1) - 1)

FIND("-", A1) は「-」が4文字目にあることを返すので、-1 して3文字を取り出しています。

区切り文字が無いと #VALUE! になります。 対処は IFERROR で包むか、FIND の結果を確認してから使います。

=IFERROR(LEFT(A1, FIND("-", A1) - 1), A1)

区切りが無ければ元の文字列をそのまま返す、という書き方です。

大文字小文字を区別しない検索

FIND は大文字小文字を区別します。区別したくないなら SEARCH を使ってください。

=LEFT(A1, SEARCH("x", A1) - 1)

SEARCH はワイルドカード（* ?）も使えます。

全角・半角の数え方

LEFTは全角も半角も1文字として数えます。

=LEFT("東京都", 2)   → 東京

バイト単位で数えたい場合は LEFTB を使います。ただし実務でバイト数が必要な場面は限られます（固定長のシステム連携など）。

日付に使うと数字になる

日付セルにLEFTを使うと、内部のシリアル値の先頭が返ります。

=LEFT(A1, 4)   → 4611   （2026/4/1 のシリアル値の先頭4桁）

日付から年を取り出したいなら TEXT か YEAR を使ってください。

=TEXT(A1, "yyyy")   → 2026
=YEAR(A1)           → 2026

LEFTは文字列専用の関数だと考えてください。

数値に使うと文字列になる

LEFTの結果は常に文字列です。計算に使うなら VALUE で戻します。

=VALUE(LEFT(A1, 3))

全行に適用する

ARRAYFORMULA で包みます。

=ARRAYFORMULA(IF(A2:A="", "", LEFT(A2:A, 3)))

SPLITやREGEXEXTRACTとの使い分け

やりたいこと	使う関数
位置が固定	LEFT
区切り文字で分ける	SPLIT
パターンで取り出す	REGEXEXTRACT

区切り文字が複数あったり、パターンが複雑なら REGEXEXTRACT のほうが素直に書けます。

=REGEXEXTRACT(A1, "^([^-]+)")

「最初の - までを取り出す」という意味です。FIND を使った書き方より短く、区切りが無くてもエラーになりません。

エラーの対処

#VALUE! が出る

• – FIND が対象を見つけられていない
• – 文字数にマイナスを指定している（FIND(...) - 1 が0未満になった）

IFERROR で包むか、FIND 単体の結果を確認してください。

想定と違う位置で切れる

対象に見えない文字（改行・全角スペース・タブ）が含まれている可能性があります。LEN() で文字数を確認すると分かります。

=LEN(A1)

見た目の文字数と合わなければ、不可視文字が入っています。TRIM や CLEAN で整えてください。

まとめ

• – 書式は =LEFT(文字列, 文字数)
• – 文字数が可変なら FIND と組み合わせる
• – 区切りが無い場合に備えて IFERROR で包む
• – 全角も半角も1文字として数える
• – 日付に使うとシリアル値が返る。TEXT を使うこと
• – パターンが複雑なら REGEXEXTRACT のほうが短い

関連する関数

• – RIGHT — 末尾から取り出す
• – MID — 途中から取り出す
• – SPLIT — 区切り文字で分割する
• – REGEXEXTRACT — 正規表現で取り出す
• – 関数一覧に戻る

TEXTJOINは、区切り文字を入れながら、複数のセルをまとめて連結する関数です。

CONCATENATE や & と違い、範囲をそのまま渡せて、空欄を自動で飛ばせます。 文字列連結の場面では、まずこれを検討してください。

書式

=TEXTJOIN(区切り文字, 空欄を無視するか, 文字列1, [文字列2], ...)

引数	内容
区切り文字	間に入れる文字。不要なら ""
空欄を無視するか	TRUE＝空欄を飛ばす / FALSE＝空欄も区切り文字を入れる
文字列	セル、範囲、直接入力のいずれも可

第2引数はほぼ TRUE で使います。

基本の使い方

	A
1	東京
2	大阪
3	（空欄）
4	名古屋

=TEXTJOIN(", ", TRUE, A1:A4)

結果：東京, 大阪, 名古屋

空欄が飛ばされているので、東京, 大阪, , 名古屋 のような区切り文字の連続が起きません。

第2引数を FALSE にすると空欄も1件として扱われます。

=TEXTJOIN(", ", FALSE, A1:A4)   → 東京, 大阪, , 名古屋

列の位置を揃えたいときだけ FALSE を使ってください。

CONCATENATEとの違い

	CONCATENATE	TEXTJOIN
範囲をそのまま渡す	不安定	できる
区切り文字	手で挟む	引数で指定
空欄を飛ばす	できない	できる

TEXTJOINで置き換えられない場面はほぼありません。

条件に合うものだけ連結する

TEXTJOINが最も活きる使い方です。FILTER と組み合わせます。

	A	B
1	東京	山田
2	大阪	鈴木
3	東京	佐藤

東京の担当者だけを並べます。

=TEXTJOIN("、", TRUE, FILTER(B1:B3, A1:A3="東京"))

結果：山田、佐藤

「1対多」の関係を1セルにまとめられるのがこの組み合わせの価値です。VLOOKUP は1件しか返せませんが、これなら該当を全部つなげられます。

支店ごとに一覧を作るなら、UNIQUE と組み合わせます。

E2:  =UNIQUE(FILTER(A1:A100, A1:A100<>""))
F2:  =TEXTJOIN("、", TRUE, FILTER($B$1:$B$100, $A$1:$A$100=E2))

F2を下にコピーすれば、支店別の担当者一覧が完成します。

改行で連結する

CHAR(10) を区切り文字にします。

=TEXTJOIN(CHAR(10), TRUE, A1:A10)

セルの書式で「折り返し」を有効にしないと、改行が見えません。 数式が正しくても1行に見えるので、まず書式を確認してください。

重複を除いて連結する

UNIQUE を挟みます。

=TEXTJOIN("、", TRUE, UNIQUE(FILTER(A1:A100, A1:A100<>"")))

並べ替えてから連結する

SORT を挟みます。

=TEXTJOIN("、", TRUE, SORT(FILTER(A1:A100, A1:A100<>"")))

FILTER → UNIQUE → SORT → TEXTJOIN の順で重ねると、「条件に合うものを、重複なしで、並べ替えて、1セルにまとめる」が1本の数式で書けます。

=TEXTJOIN("、", TRUE, SORT(UNIQUE(FILTER(B1:B100, A1:A100="東京"))))

日付や数値を連結する

そのまま渡すと内部の数値が出ます。TEXT で書式を指定してください。

=TEXTJOIN("、", TRUE, ARRAYFORMULA(TEXT(A1:A10, "m/d")))

ARRAYFORMULA で包むことで、範囲の全要素に TEXT が効きます。

注意点

結果の文字数に上限があります。 1セルに入る文字数（約5万文字）を超えるとエラーになります。数千件を連結するような使い方はできません。

元データが変わると結果も変わります。 これは利点でもありますが、「その時点の一覧」を保存したいなら、結果をコピーして値貼り付けしてください。

エラーの対処

#VALUE! が出る

• – 区切り文字が指定されていない（"" でも必ず書く）
• – 第2引数が抜けている

引数が3つ必要な点を確認してください。

空欄が飛ばされない

第2引数が FALSE になっています。TRUE にしてください。

なお、数式が返した ""（空文字）は空欄として扱われるので、TRUE なら飛ばされます。

結果が長すぎて切れる

文字数の上限に達しています。FILTER で件数を絞るか、複数セルに分割してください。

Excelとの違い

TEXTJOINはExcelにもあります（Microsoft 365 / Excel 2019以降）。書式・挙動ともに同じです。

Excel 2016以前では使えません。その場合は CONCATENATE と & で書くか、作業列を使った方法になります。

条件付き連結（FILTER との組み合わせ）は、ExcelでもFILTER関数が使える環境（365/2021以降）なら同じ書き方ができます。

まとめ

• – 書式は =TEXTJOIN(区切り文字, TRUE, 範囲)
• – 範囲をそのまま渡せて、空欄を自動で飛ばせる
• – CONCATENATE の上位互換。基本はこちらを使う
• – FILTER と組み合わせると条件付き連結ができる
• – FILTER → UNIQUE → SORT → TEXTJOIN で1対多の一覧が1本の数式に
• – 改行は CHAR(10)。セルの折り返し設定も必要

関連する関数

• – FILTER — 条件で絞ってから連結する
• – UNIQUE — 重複を除く
• – SORT — 並べ替える
• – TEXT — 日付・数値の書式を整える
• – 関数一覧に戻る

CONCATENATEは、複数の文字列をつなげる関数です。

「姓」と「名」を結合してフルネームにする、住所の各項目をつなげる、といった処理に使います。

ただし実務では、& 演算子か TEXTJOIN を使うほうが多いのが実情です。この記事では使い分けまで整理します。

書式

=CONCATENATE(文字列1, [文字列2], ...)

基本の使い方

	A	B
1	山田	太郎

=CONCATENATE(A1, B1)      → 山田太郎
=CONCATENATE(A1, " ", B1) → 山田 太郎

区切り文字を入れたいときは、間に文字列を挟みます。

&演算子との違い

同じことが & でも書けます。

=A1 & B1
=A1 & " " & B1

結果はまったく同じです。 どちらを使うかは好みの問題ですが、実務では次のような使い分けが一般的です。

	CONCATENATE	&
短い連結	冗長	簡潔
項目が多い	見やすい	& だらけで読みにくい
数式内での可読性	括弧が必要	直感的

2〜3個なら &、それ以上なら関数、というのが目安です。

範囲を渡したときの挙動

ここが最大の注意点です。

=CONCATENATE(A1:A5)

この書き方は、期待通りに動きません。 スプレッドシートでは範囲を渡すと、最初のセルだけ、または配列として展開されるなど、環境によって挙動が不安定です。

範囲をまとめて連結したいなら、TEXTJOIN を使ってください。

=TEXTJOIN(", ", TRUE, A1:A5)

区切り文字を指定でき、空欄を無視でき、範囲をそのまま渡せます。CONCATENATEの上位互換と考えて構いません。

CONCATとの違い

CONCAT という似た関数もあります。

=CONCAT(A1, B1)

引数を2つしか取れません。 3つ以上つなげたい場合はCONCATENATEか & を使ってください。

Excelでは CONCAT が範囲に対応していますが、スプレッドシートの CONCAT は2引数専用です。同名でも挙動が違うので注意してください。

日付や数値が崩れる原因

日付をそのまま連結すると、内部の数値（シリアル値）が出てしまいます。

=CONCATENATE("更新日：", A1)   → 更新日：46113

TEXT で書式を指定してから連結してください。

=CONCATENATE("更新日：", TEXT(A1, "yyyy/m/d"))   → 更新日：2026/4/1

金額も同様です。

=CONCATENATE("合計：", TEXT(A1, "¥#,##0"))   → 合計：¥1,234,567

桁区切りやゼロ埋めは、連結すると消えます。 表示形式はセルの設定であって、値そのものではないためです。ここは実務で頻繁に起きるトラブルです。

改行を入れる

CHAR(10) が改行です。

=CONCATENATE(A1, CHAR(10), B1)

セルの書式で「折り返し」を有効にしないと、改行が見えません。 数式は正しくても表示されないので、書式を確認してください。

全行に適用する

ARRAYFORMULA と組み合わせられますが、& のほうが確実に動きます。

=ARRAYFORMULA(IF(A2:A="", "", A2:A & " " & B2:B))

CONCATENATEは配列に対する挙動が安定しないため、ARRAYFORMULA の中では & を使ってください。

使い分けのまとめ

やりたいこと	使うもの
2〜3個のセルをつなぐ	&
項目が多く、可読性を上げたい	CONCATENATE
範囲をまとめて連結	TEXTJOIN
区切り文字を入れて連結	TEXTJOIN
空欄を飛ばして連結	TEXTJOIN
ARRAYFORMULA の中	&

迷ったら TEXTJOIN を検討してください。 CONCATENATEでしかできないことは、実質ありません。

Excelとの違い

CONCATENATE・&・TEXTJOIN はすべて共通です。

違いは CONCAT です。

	Excel	スプレッドシート
CONCAT の引数	範囲も可	2つまで
CONCATENATE	非推奨（CONCAT 推奨）	現役

ExcelではCONCATENATEは互換性のために残されている扱いですが、スプレッドシートでは普通に使えます。ただしどちらの環境でも、& か TEXTJOIN を使うのが現代的です。

まとめ

• – 書式は =CONCATENATE(文字列1, 文字列2, ...)
• – & とまったく同じ結果。2〜3個なら & が簡潔
• – 範囲を渡すと期待通りに動かない → TEXTJOIN を使う
• – 日付・金額は TEXT で書式を指定してから連結する
• – 改行は CHAR(10)。セルの折り返し設定も必要
• – ARRAYFORMULA の中では & を使う

関連する関数

• – TEXTJOIN — 区切り文字付き・空欄無視で範囲を連結
• – TEXT — 日付や数値の書式を指定する
• – SPLIT — 逆に文字列を分割する
• – SUBSTITUTE — 文字列を置き換える
• – 関数一覧に戻る

TEXTは、数値や日付を、指定した形式の文字列に変換する関数です。

「2026/4/1」を「2026年4月1日（水）」にする、1234567 を ¥1,234,567 にする、といった変換ができます。

書式設定（表示形式）との違いは、TEXTが文字列そのものを作る点です。他の文字列と連結できる代わりに、計算には使えなくなります。

書式

=TEXT(値, "書式")

書式は必ず " で囲みます。

日付の書式

書式	結果（2026年4月1日 水曜の場合）
"yyyy/mm/dd"	2026/04/01
"yyyy年m月d日"	2026年4月1日
"yy/m/d"	26/4/1
"mm/dd"	04/01
"m月d日"	4月1日
"aaa"	水
"aaaa"	水曜日
"ddd"	Wed
"dddd"	Wednesday
"yyyy年m月d日(aaa)"	2026年4月1日(水)

m を1つにすると1桁のとき「4」、2つにすると「04」になります。

曜日を取り出すのがTEXTの最頻出の用途です。

=TEXT(A1, "aaa")   → 水

WEEKDAY で番号を取って SWITCH で変換する方法もありますが、TEXTのほうが圧倒的に短く書けます。

時刻の書式

書式	結果（15:05:30の場合）
"h:mm"	15:05
"hh:mm:ss"	15:05:30
"h時m分"	15時5分
"[h]:mm"	経過時間として24時間超も表示

[h] は重要です。 通常の h は24時間で0に戻りますが、[h] なら「30:00」のように24時間を超えた表示ができます。勤務時間の合計を出すときに必須です。

=TEXT(SUM(B2:B100), "[h]:mm")

数値の書式

書式	結果（1234567.891の場合）
"#,##0"	1,234,568
"#,##0.00"	1,234,567.89
"¥#,##0"	¥1,234,568
"0.0%"	（0.156なら）15.6%
"0000"	（42なら）0042

0 と # の違い

• – 0 … 桁がなくても0を表示する
• – # … 桁がなければ何も表示しない

"0000" は桁を揃えるゼロ埋めに使います。商品コードや伝票番号の整形で便利です。

=TEXT(A1, "0000")   → 42 が 0042 になる

正負で表示を変える

セミコロンで区切ると、正の数・負の数・ゼロで書式を分けられます。

=TEXT(A1, "#,##0;▲#,##0;-")

正なら 1,234、負なら ▲1,234、ゼロなら - になります。会計資料でよく使われる形式です。

文字列との連結

TEXTの主な用途がこれです。& で日付や数値をそのままつなぐと、内部の数値（シリアル値）が出てしまいます。

="更新日：" & A1        → 更新日：46113   （シリアル値）
="更新日：" & TEXT(A1, "yyyy/m/d")   → 更新日：2026/4/1

日付や金額を文章に混ぜるときは、必ずTEXTで包んでください。

表示形式との使い分け

	表示形式（メニュー）	TEXT関数
中身	数値のまま	文字列になる
計算に使える	使える	使えない
他の文字列と連結	シリアル値が出る	きれいに連結できる
並べ替え	正しく並ぶ	文字列順になる

原則は「表示形式を使う」です。 TEXTを使うのは、文字列として連結したいときだけにしてください。

TEXTで作った日付を並べ替えると、2026/1/1 より 2026/10/1 が先に来ます。文字列として比較されるためです。集計や並べ替えの対象になる列にTEXTを使うと、後で必ず困ります。

文字列を数値に戻す

TEXTの結果を計算に使いたくなったら、VALUE で戻します。

=VALUE(TEXT(A1, "yyyy/mm/dd"))

ただし、そもそもTEXTを挟まないほうが確実です。

Excelとの違い

基本の書式指定は共通です。 ただし、いくつか違いがあります。

書式	Excel	スプレッドシート
"aaa"（日本語の曜日）	使える	使える
"ggge年"（和暦）	使える	使えない
[h]（24時間超）	使える	使える

和暦（令和・平成）はスプレッドシートのTEXTでは扱えません。 必要なら IFS や対応表で自作するか、Excelを使ってください。

また、ロケール設定によって書式記号の解釈が変わることがあります。ファイルの設定を「日本」にしておくと想定通りに動きます。

よくある失敗

結果が左寄せになって計算できない

TEXTは文字列を返します。 これは仕様です。計算する列には使わないでください。

並べ替えがおかしい

文字列として比較されています。元の日付列で並べ替えてください。

書式が効かない

• – 書式を " で囲んでいない
• – 対象が日付ではなく文字列になっている（左寄せになっていないか確認）

日付が文字列だと、TEXTは何もできません。まず DATEVALUE で日付に変換してください。

#VALUE! が出る

対象が数値でも日付でもない文字列です。

まとめ

• – 書式は =TEXT(値, "書式")
• – 曜日は "aaa" が最短。TEXTの最頻出用途
• – 24時間超の時間は [h]:mm
• – ゼロ埋めは "0000"
• – 結果は文字列になり、計算・並べ替えに使えない
• – 見た目を変えるだけなら表示形式（メニュー）を使う

関連する関数

• – TEXTJOIN — 複数の文字列を区切り文字で連結
• – CONCATENATE — 文字列の連結
• – TODAY — 今日の日付
• – DATEDIF — 日付の差を求める
• – 関数一覧に戻る

FLATTENは、複数の行・列にまたがるデータを、1列にまとめる関数です。

横に広がった表を縦一列にしたい、複数の列をまとめて1つのリストにしたい——こうした「形を変える」処理に使います。

Excelにはありません。 スプレッドシート固有の関数です。

書式

=FLATTEN(範囲1, [範囲2], ...)

範囲はいくつでも並べられます。

基本の動き

	A	B	C
1	東京	大阪	名古屋
2	福岡	札幌	仙台

=FLATTEN(A1:C2)

結果（縦一列）： 東京 / 大阪 / 名古屋 / 福岡 / 札幌 / 仙台

行ごとに、左から右へ読んで縦に並べます。 読む順番はこの通りで固定です。

離れた範囲をまとめる

=FLATTEN(A1:A10, C1:C10, E1:E10)

3つの列が1列に連結されます。

縦に結合するだけなら {} でも書けます。

={A1:A10; C1:C10; E1:E10}

; が縦方向の結合です。範囲の列数が揃っているならこちらでも構いません。列数が揃っていない場合や、複数列を1列にしたい場合にFLATTENが必要になります。

空欄を除く

範囲を大きく取ると、空欄もそのまま結果に入ります。FILTER で除きます。

=FILTER(FLATTEN(A1:C100), FLATTEN(A1:C100)<>"")

FLATTENを2回書く必要があるのが冗長ですが、これが標準的な書き方です。

重複も除くなら UNIQUE を重ねます。

=UNIQUE(FILTER(FLATTEN(A1:C100), FLATTEN(A1:C100)<>""))

複数列に散らばった値から、重複なしの一覧を作る——これがFLATTENの最も実用的な使い方です。

横持ちを縦持ちに変換する

月ごとに列が伸びていく「横持ち」の表は、集計に向きません。FLATTENで縦持ちに直せます。

元の表（横持ち）

	A	B	C	D
1	支店	4月	5月	6月
2	東京	1200	1400	1300
3	大阪	800	950	900

縦持ちに変換する

支店の列（各支店を月数ぶん繰り返す）：

=FLATTEN(A2:A3 & {"","",""})

& で3列に複製してからFLATTENしています。

月の列：

=FLATTEN(IF(A2:A3<>"", {B1:D1; B1:D1}, ""))

金額の列：

=FLATTEN(B2:D3)

3つを横に並べると、縦持ちの表ができます。

縦持ちにしておくと、SUMIFS や QUERY で自由に切り出せます。 横持ちのままだと、月が増えるたびに数式を直す必要があります。

複数シートをまとめる

同じ形のシートが並んでいる場合、{} で縦に結合するのが基本です。

={'4月'!A2:C100; '5月'!A2:C100; '6月'!A2:C100}

空行が入るので FILTER で除きます。

=FILTER({'4月'!A2:C100; '5月'!A2:C100}, {'4月'!A2:A100; '5月'!A2:A100}<>"")

この用途ではFLATTENは使いません。FLATTENは「複数列を1列にする」ための関数で、「複数の表を縦に積む」のは {} の役割です。ここは混同しやすいので区別してください。

エラーの対処

#REF! が出る

結果の展開先にデータが入っています。 FLATTENは下方向に結果を広げるため、その領域が空いている必要があります。

空欄が大量に出る

範囲を大きく取りすぎています。FILTER で <>"" を指定してください。

順番が想定と違う

FLATTENは行ごとに左から右の順で読みます。列ごとに読みたい場合は、TRANSPOSE で行列を入れ替えてから渡してください。

=FLATTEN(TRANSPOSE(A1:C2))

まとめ

• – 書式は =FLATTEN(範囲)。行ごとに左から右の順で1列にまとめる
• – 空欄を除くには FILTER で <>""
• – 複数列から重複なしの一覧を作るのが主な用途
• – 横持ちを縦持ちに変換できる。集計しやすい形になる
• – 複数の表を縦に積むのは {} の役割。FLATTENとは別物
• – Excelには無い

関連する関数

• – FILTER — 空欄を除く
• – UNIQUE — 重複を除く
• – QUERY — 縦持ちにした後の集計
• – SPLIT — 逆に1セルを複数に分ける
• – 関数一覧に戻る

SPLITは、1つのセルの文字列を、区切り文字で複数のセルに分割する関数です。

「東京都新宿区西新宿」を都道府県と市区町村に分ける、カンマ区切りのデータを列に展開する、といった場面で使います。

Excelにはありません。 Excelでは「区切り位置」機能か TEXTSPLIT（365のみ）を使います。

書式

=SPLIT(文字列, 区切り文字, [各文字で区切るか], [空のセルを削除するか])

引数	内容
文字列	分割する対象
区切り文字	区切りに使う文字
各文字で区切るか	省略時 TRUE。区切り文字の1文字ずつで区切る
空のセルを削除するか	省略時 TRUE

第3引数の挙動が独特なので、後で詳しく説明します。

基本の使い方

A1に 東京,大阪,名古屋 が入っているとします。

=SPLIT(A1, ",")

結果： 3つのセルに「東京」「大阪」「名古屋」が横方向に展開されます。

結果は右方向に広がります。 右隣にデータがあると #REF! になるので、空けておいてください。

第3引数の落とし穴

区切り文字を複数指定したときの挙動が独特です。

A1に 東京-大阪_名古屋 が入っているとします。

=SPLIT(A1, "-_")

結果： 東京 / 大阪 / 名古屋

"-_" という2文字の並びではなく、「-」と「_」のどちらでも区切るという意味になります。これが第3引数の既定（TRUE）の挙動です。

「-_」という2文字の並びで区切りたいなら、第3引数を FALSE にします。

=SPLIT(A1, "-_", FALSE)

複数文字の区切り（", " や " - " など）を使うときは、必ず FALSE を指定してください。 ここを忘れると、スペースでも区切られてしまいます。

連続した区切りの扱い

A1に 東京,,名古屋 が入っている場合（大阪が欠損）。

=SPLIT(A1, ",")        → 東京 / 名古屋   （2つ）
=SPLIT(A1, ",", TRUE, FALSE)  → 東京 / （空） / 名古屋   （3つ）

第4引数を FALSE にすると、空の要素を保持します。

列の位置を揃えたいときは FALSE が必須です。既定のままだと、欠損がある行だけ列がずれます。CSVを扱うときに必ず問題になる箇所です。

特定の位置だけ取り出す

SPLITの結果から1つだけ欲しいときは、INDEX で包みます。

=INDEX(SPLIT(A1, ","), 1, 2)

「1行目・2番目」＝2つ目の要素です。

この形なら結果が1セルに収まるので、右方向に展開されません。表の中で使うときはこちらが便利です。

全行に適用する

SPLITはARRAYFORMULAと相性が悪く、複数行をまとめて分割することはできません。

行ごとに数式を置くか、次のように INDEX と組み合わせて列ごとに書きます。

B2:  =ARRAYFORMULA(IF(A2:A="", "", INDEX(SPLIT(A2:A, ","), 0, 1)))

環境によって挙動が安定しないため、確実にやるなら数式を各行にコピーするか、REGEXEXTRACT を使ってください。

=ARRAYFORMULA(IF(A2:A="", "", REGEXEXTRACT(A2:A, "^([^,]+)")))

正規表現なら ARRAYFORMULA が確実に効きます。

縦に並べたい

SPLITの結果は横に出ます。縦にしたいなら TRANSPOSE で包みます。

=TRANSPOSE(SPLIT(A1, ","))

複数行をまとめて縦一列にするなら FLATTEN を使います。

実用例

氏名を姓と名に分ける

=SPLIT(A1, " ")

全角スペースの場合は "　" を指定してください。両方に対応するなら第3引数を TRUE のままにして両方渡します。

=SPLIT(A1, " 　")

半角スペースと全角スペースのどちらでも区切られます。

メールアドレスのドメインを取り出す

=INDEX(SPLIT(A1, "@"), 1, 2)

URLからドメインを取り出す

=INDEX(SPLIT(A1, "/"), 1, 3)

https://example.com/page を / で区切ると、1番目が https:、2番目が空（既定では削除される）、3番目が example.com です。第4引数の扱いで位置が変わるので、実際の結果を確認してください。

エラーの対処

#REF! が出る

結果の展開先にデータが入っています。 SPLITは右方向に広がるので、右隣を空けてください。

分割されない

• – 区切り文字が実際のデータと違う（全角カンマ ， と半角カンマ ,）
• – 区切り文字がセル内に存在しない

LEN() で文字数を確認するか、SUBSTITUTE で置き換えてから分割すると解決することがあります。

意図しない場所で区切られる

第3引数が既定の TRUE のままで、複数文字の区切りを指定している可能性があります。FALSE にしてください。

列がずれる

第4引数を FALSE にして、空の要素を保持してください。

まとめ

• – 書式は =SPLIT(文字列, 区切り文字)。結果は右方向に展開
• – 複数文字の区切りを使うなら第3引数を FALSE
• – 列の位置を揃えたいなら第4引数を FALSE
• – 1つだけ取り出すなら INDEX(SPLIT(...), 1, n)
• – 全行に適用するなら REGEXEXTRACT のほうが確実
• – Excelには無い（「区切り位置」機能か TEXTSPLIT）

関連する関数

• – REGEXEXTRACT — 正規表現で取り出す。全行適用に強い
• – INDEX — 分割結果から1つ選ぶ
• – FLATTEN — 複数の結果を縦一列にする
• – SUBSTITUTE — 区切り文字を統一する
• – 関数一覧に戻る

UNIQUEは、重複を除いた一覧を返す関数です。

「支店の一覧が欲しい」「登場した商品名だけ知りたい」というときに、1つの数式で済みます。元の表には手を触れません。

書式

=UNIQUE(範囲)

引数は範囲だけです。

基本の使い方

	A
1	東京
2	大阪
3	東京
4	名古屋
5	大阪

=UNIQUE(A1:A5)

結果： 東京 / 大阪 / 名古屋

出てきた順番が保たれます。 五十音順にはなりません。並べ替えたいなら SORT で包みます。

=SORT(UNIQUE(A1:A5))

空行が入る原因と対処

範囲を A2:A1000 のように大きく取ると、空欄も1つの値として結果に含まれます。

対処は FILTER で空を除くことです。

=UNIQUE(FILTER(A2:A1000, A2:A1000<>""))

この形が実用上の基本形になります。列全体（A:A）を使うときは特に必要です。

複数列での重複判定

範囲を複数列にすると、行全体が同じものだけを重複と見なします。

	A	B
1	東京	A
2	東京	B
3	東京	A

=UNIQUE(A1:B3)

結果： 東京/A、東京/B の2行。1行目と3行目が同一なので1つにまとまります。

「支店 × 商品」の組み合わせ一覧が作りたいときに使えます。

件数を数える

重複を除いた件数は COUNTA と組み合わせます。

=COUNTA(UNIQUE(A2:A100))

空欄を除くなら FILTER を挟んでください。

=COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>"")))

集計表の見出しを作る

UNIQUEの実務での主な用途がこれです。

E2に  =UNIQUE(FILTER(A2:A1000, A2:A1000<>""))
F2に  =ARRAYFORMULA(IF(E2:E="", "", SUMIF(A:A, E2:E, C:C)))

支店の一覧が自動で作られ、その横に合計が出ます。 新しい支店のデータが増えても、一覧が自動で伸びます。

ただし、この用途なら QUERY 1行のほうが短く書けます。

=QUERY(A:C, "select A, sum(C) where A <> '' group by A", 1)

集計まで含むならQUERY、一覧だけならUNIQUE、という使い分けになります。

重複している値のほうを知りたい

UNIQUEは重複を「除く」関数なので、逆はできません。重複しているものを見つけるなら COUNTIF を使います。

=IF(COUNTIF(A:A, A2) > 1, "重複", "")

重複している値の一覧が欲しいなら、組み合わせます。

=UNIQUE(FILTER(A2:A100, COUNTIF(A2:A100, A2:A100) > 1))

実際に重複行を削除したい

UNIQUEは表示するだけで、元データは変わりません。元データから重複を消したいなら、次のいずれかです。

• – メニューの「データ → データクリーンアップ → 重複を削除」
• – UNIQUEの結果をコピーして「値のみ貼り付け」で置き換える

元データを直接いじる前に、必ずコピーを取ってください。 UNIQUEで結果を確認してから実行するのが安全です。

エラーの対処

#REF! が出る

結果の展開先にデータが入っています。 UNIQUEは下方向に結果を広げるため、その領域が空いている必要があります。

空欄が結果に含まれる

FILTER で <>"" を指定してください。

同じに見えるのに別扱いされる

• – 前後にスペースが入っている（"東京 " と "東京"）
• – 全角と半角が違う
• – 数値と文字列が混ざっている

TRIM で整えると解決することが多いです。

=UNIQUE(ARRAYFORMULA(TRIM(A2:A100)))

大文字小文字が区別されない

UNIQUEは大文字小文字を区別しません。"abc" と "ABC" は同じ値として扱われます。区別したい場合は、EXACT を使った判定に置き換える必要があります。

Excelとの違い

Excelにも UNIQUE があります（Microsoft 365 / Excel 2021以降）。書式はほぼ同じですが、Excelには追加の引数があります。

	Excel	スプレッドシート
列方向の重複除去	第2引数で指定可	範囲の向きで決まる
1回だけ出現した値のみ	第3引数で指定可	無い

Excel 2019以前では使えないので、その場合は「重複の削除」機能か、COUNTIF を使った作業列で対応します。

まとめ

• – 書式は =UNIQUE(範囲)。出てきた順が保たれる
• – 実用上の基本形は =UNIQUE(FILTER(A2:A, A2:A<>""))
• – 複数列を指定すると、行全体が同じものを重複と見なす
• – 件数は =COUNTA(UNIQUE(...))
• – 集計まで含むなら QUERY のほうが短い
• – 元データは変わらない。削除したいならメニュー機能を使う

関連する関数

• – FILTER — 空欄を除くのに必須
• – SORT — 結果を並べ替える
• – COUNTIF — 重複している側を見つける
• – QUERY — 一覧＋集計をまとめて
• – 関数一覧に戻る