Excelで「氏名から姓だけ取り出したい」「カンマ区切りのデータを列に分けたい」と悩んだことはありませんか。文字列関数は種類が多く、どれを使えばいいか迷いますよね。
この記事では、抽出・結合・分割・置換の4カテゴリに分けて文字列関数の使い分けを整理しました。実務シーンに合わせて「この作業にはこの関数」がすぐわかる逆引きインデックスも用意しています。
文字列関数の逆引きインデックス【やりたいこと別】
まずは「やりたいこと」から使う関数を探せる一覧表です。
| やりたいこと | 使う関数 | 数式の例 | |
|---|---|---|---|
| 左端からN文字を取り出す | LEFT | =LEFT(A1,3) | |
| 右端からN文字を取り出す | RIGHT | =RIGHT(A1,4) | |
| 途中からN文字を取り出す | [[2023-01-02-excel-function-howto-use-mid | MID]] | =MID(A1,2,3) |
| 特定の文字の位置を調べる | FIND / SEARCH | =FIND("@",A1) | |
| 文字数を数える | [[2022-03-21-excel-function-howto-use-len | LEN]] | =LEN(A1) |
| 文字列をそのまま結合する | [[2022-07-24-excel-function-howto-use-concat | CONCAT]] / & | =A1&B1 |
| 区切り文字を入れて結合する | [[2022-05-10-excel-function-howto-use-textjoin | TEXTJOIN]] | =TEXTJOIN(",",TRUE,A1:C1) |
| 区切り文字で列に分割する | [[2022-12-22-excel-function-howto-use-textsplit | TEXTSPLIT]] | =TEXTSPLIT(A1,",") |
| 区切り文字の前だけ取り出す | [[2022-12-23-excel-function-howto-use-textbefore | TEXTBEFORE]] | =TEXTBEFORE(A1,"@") |
| 区切り文字の後だけ取り出す | [[2022-12-24-excel-function-howto-use-textafter | TEXTAFTER]] | =TEXTAFTER(A1,"@") |
| 特定の文字列を別の文字列に置き換える | [[2022-05-02-excel-function-howto-use-substitute | SUBSTITUTE]] | =SUBSTITUTE(A1,"-","") |
| 位置を指定して文字列を置き換える | REPLACE | =REPLACE(A1,1,3,"新") | |
| 余分なスペースを除去する | [[2022-08-03-excel-function-howto-use-trim | TRIM]] | =TRIM(A1) |
| 大文字に変換する | [[2022-05-05-excel-function-howto-use-upper | UPPER]] | =UPPER(A1) |
| 小文字に変換する | [[2022-05-07-excel-function-howto-use-lower | LOWER]] | =LOWER(A1) |
| 先頭だけ大文字にする | [[2022-05-09-excel-function-howto-use-proper | PROPER]] | =PROPER(A1) |
| 全角を半角に変換する | [[2022-09-02-excel-function-howto-use-asc | ASC]] | =ASC(A1) |
| 数値を指定の表示形式に変換する | [[2022-11-15-excel-function-howto-use-text | TEXT]] | =TEXT(A1,"#,##0") |
TIP
このインデックスをブックマークしておくと便利です。「どの関数だっけ？」と迷ったときにすぐ確認できます。
文字列を「抽出」する関数の使い方
文字列の一部だけを取り出すには、LEFT・RIGHT・MID・FIND（SEARCH）を組み合わせます。
LEFT関数 + FIND関数で姓だけ取り出す
氏名リストから姓だけを取り出すケースです。「山田 太郎」のようにスペースで区切られた氏名を想定します。
=LEFT(A1,FIND(" ",A1)-1)この数式がやっていることはシンプルです。
FIND(" ",A1)でスペースの位置を調べる（例: 3）- スペースの手前までが姓なので
-1する（例: 2） LEFT(A1,2)で左端から2文字を取り出す（例: 山田）
LEFT関数は「左端から何文字」と決まっている場合にそのまま使えます。たとえば左端から3文字なら次のように書きます。
=LEFT(A1,3)NOTE
FIND関数は大文字と小文字を区別します。区別せずに検索したい場合は、代わりにSEARCH関数を使ってください。SEARCH関数はワイルドカード（
*や?）にも対応しています。
RIGHT関数 + LEN関数で名だけ取り出す
今度は名前の方を取り出してみましょう。RIGHT関数で右端から取り出します。
=RIGHT(A1,LEN(A1)-FIND(" ",A1))取り出す文字数の計算がポイントです。
LEN(A1)で全体の文字数を取得（例: 5）FIND(" ",A1)でスペースの位置を取得（例: 3）- 全体の文字数からスペースの位置を引く（例: 5-3=2）
RIGHT(A1,2)で右端から2文字を取り出す（例: 太郎）
MID関数で途中の文字列を取り出す
MID関数は「何文字目から何文字」を指定して取り出せます。
5文字目から4文字を取り出す場合:
=MID(A1,5,4)たとえば社員番号「DEPT-1234-A」から数字部分だけを取り出す場合です。6文字目から4文字で「1234」が得られます。
=MID(A1,6,4)MID関数は開始位置と文字数が固定の場合に便利です。位置が可変の場合はFIND関数と組み合わせます。
FIND関数とSEARCH関数の違い
どちらも「特定の文字が何文字目にあるか」を返す関数です。
| 比較項目 | FIND | SEARCH |
|---|---|---|
| 大文字・小文字 | 区別する | 区別しない |
| ワイルドカード | 使えない | * ? が使える |
| 用途 | 完全一致で位置を調べる | あいまい検索で位置を調べる |
実務では「スペースの位置を調べる」「@の位置を調べる」のような用途が多いため、FIND関数で十分な場面がほとんどです。
M365ならTEXTBEFORE・TEXTAFTERが便利
Microsoft 365をお使いなら、LEFT+FINDの組み合わせを1つの関数で置き換えられます。
スペースの前（姓）を取り出す場合:
=TEXTBEFORE(A1," ")スペースの後（名）を取り出す場合:
=TEXTAFTER(A1," ")TEXTBEFORE関数とTEXTAFTER関数は区切り文字を指定するだけでOKです。LEFT+FIND+LENの複雑な計算が不要になります。
NOTE
TEXTBEFORE・TEXTAFTER・TEXTSPLITはMicrosoft 365専用の関数です。Excel 2021以前では使用できません。
文字列を「結合」する関数の使い方
複数のセルの値をひとつにまとめるには、&演算子・CONCAT・TEXTJOIN・CONCATENATEを使います。
&演算子でシンプルに結合する
もっとも基本的な結合方法です。
姓と名をスペース区切りで結合する場合:
=A1&" "&B1セルの数が少ない（2~3個）場合は&演算子がわかりやすいです。ハイフン区切りで3つ結合するならこう書きます。
=A1&"-"&B1&"-"&C1CONCAT関数でセル範囲を一括結合する
CONCAT関数はセル範囲をまるごと結合できます。
A1からD1まで一括結合する場合:
=CONCAT(A1:D1)&演算子と違い、範囲指定ができるのがメリットです。ただし区切り文字を自動で入れる機能はありません。
TEXTJOIN関数で区切り文字付きの結合をする
TEXTJOIN関数は区切り文字を指定して結合できます。
=TEXTJOIN(",",TRUE,A1:D1)各引数の意味はこちらです。
- 第1引数: 区切り文字（カンマ、スペース、ハイフンなど）
- 第2引数: 空セルを無視するか（TRUE = 無視する）
- 第3引数: 結合するセル範囲
空セルを無視してくれるのが大きなポイントです。たとえばA1~D1のうちC1が空白でも、余計な区切り文字が入りません。
A1=東京, B1=大阪, C1=(空白), D1=福岡 の場合で比べてみましょう。
| 数式 | 結果 |
|---|---|
=TEXTJOIN(",",TRUE,A1:D1) | 東京,大阪,福岡 |
=TEXTJOIN(",",FALSE,A1:D1) | 東京,大阪,,福岡 |
TRUEにすると空セルのC1が飛ばされ、余計なカンマが入りません。
結合関数の選び方
| 比較項目 | & | [[2022-07-24-excel-function-howto-use-concat | CONCAT]] | [[2022-06-30-excel-function-howto-use-concatenate | CONCATENATE]] | [[2022-05-10-excel-function-howto-use-textjoin | TEXTJOIN]] |
|---|---|---|---|---|---|---|---|
| 範囲指定 | 不可 | 可能 | 不可 | 可能 | |||
| 区切り文字 | 手動で挿入 | なし | なし | 自動挿入 | |||
| 空セル無視 | 不可 | 不可 | 不可 | 可能 | |||
| 対応バージョン | 全バージョン | Excel 2019以降 | 全バージョン | Excel 2019以降 | |||
| おすすめ度 | 2~3個の結合 | 範囲一括 | 旧環境向け | 区切り文字付き結合 |
TIP
CONCATENATEは互換性のために残されている旧関数です。新しい環境ではCONCAT関数を使ってください。詳しくは「CONCAT関数の使い方」で解説しています。
文字列を「分割」する関数の使い方
1つのセルに入っている文字列を複数のセルに分けたい場面は多いです。TEXTSPLIT関数やLEFT+FIND関数の組み合わせで対応できます。
TEXTSPLIT関数でカンマ区切りデータを列に分ける
TEXTSPLIT関数はM365で追加された強力な関数です。
=TEXTSPLIT(A1,",")カンマ区切りの「東京,大阪,名古屋,福岡」を入力すると、4つのセルに自動で分割されます。スピル機能で横方向に展開されるので、数式は1つだけでOKです。
行方向に分割したい場合は、第3引数を使います。
第3引数に区切り文字を指定すると行方向に分割されます。
=TEXTSPLIT(A1,,",")複数の区切り文字を同時に指定することも可能です。カンマとセミコロンの両方で分割する場合はこう書きます。
=TEXTSPLIT(A1,{",",";"})TEXTSPLIT が使えない環境での分割テクニック
Excel 2021以前ではTEXTSPLIT関数が使えません。その場合はLEFT+FIND+MID+LENを組み合わせます。
1つ目の要素（カンマの前）を取り出す場合:
=LEFT(A1,FIND(",",A1)-1)2つ目の要素（1つ目のカンマ～2つ目のカンマの間）を取り出す場合:
=MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-1)ちょっと複雑に見えますが、やっていることはシンプルです。
- 1つ目のカンマの位置を調べる
- 2つ目のカンマの位置を調べる（FINDの第3引数で開始位置を指定）
- 2つの位置の間の文字列をMIDで取り出す
NOTE
要素数が多い場合は数式が非常に複雑になります。M365環境であればTEXTSPLIT関数の利用を強くおすすめします。
「区切り位置」機能との比較
Excelには関数以外にも「区切り位置」というメニュー機能があります。
| 比較項目 | TEXTSPLIT関数 | 区切り位置（メニュー） |
|---|---|---|
| 操作方法 | 数式を入力 | データ > 区切り位置 |
| 元データ | 保持される | 上書きされる |
| 自動更新 | 元データが変わると自動反映 | 手動で再実行が必要 |
| 対応バージョン | Microsoft 365 | 全バージョン |
| 大量データ | 数式1つで対応可 | 毎回手動操作 |
元データを残しつつ自動更新したい場合はTEXTSPLIT関数が圧倒的に便利です。一度きりの変換なら区切り位置機能でも十分対応できます。
文字列を「置換・整形」する関数の使い方
文字列を別の文字列に置き換えたり、余分なスペースを取り除く場面で活躍します。SUBSTITUTE・REPLACE・TRIM関数が代表的です。
SUBSTITUTE関数で特定の文字列を置き換える
SUBSTITUTE関数は「この文字列をこの文字列に変える」という置換に使います。
住所から「東京都」を除く場合:
=SUBSTITUTE(A1,"東京都","")電話番号のハイフンを除去する場合:
=SUBSTITUTE(A1,"-","")第4引数を指定すると、何番目の出現だけを置換できます。
1番目のハイフンだけをスラッシュに変換する場合:
=SUBSTITUTE(A1,"-","/",1)REPLACE関数で位置を指定して置き換える
REPLACE関数は「何文字目から何文字」を指定して置き換えます。
1文字目から3文字を「ABC」に置換する場合:
=REPLACE(A1,1,3,"ABC")SUBSTITUTEとREPLACEの使い分けはこちらです。
| 比較項目 | [[2022-05-02-excel-function-howto-use-substitute | SUBSTITUTE]] | REPLACE |
|---|---|---|---|
| 指定方法 | 置き換える文字列 | 位置と文字数 | |
| 用途 | 「この文字を消したい」 | 「何文字目を変えたい」 | |
| 全出現の置換 | 可能（第4引数で制御） | 1箇所のみ | |
| 文字列が不明な場合 | 使えない | 位置さえわかればOK |
実務では「ハイフンを消す」「都道府県を除く」のようにSUBSTITUTE関数を使う場面が多いです。
TRIM関数でスペースを整える
TRIM関数は文字列の前後のスペースを除去し、単語間のスペースを1つに統一します。
=TRIM(A1)たとえば「 山田 太郎 」が「山田 太郎」になります。データのコピー&ペーストで余分なスペースが入ってしまったときに重宝します。
TIP
TRIM関数は半角スペースのみ対応です。全角スペースを除去したい場合は
=SUBSTITUTE(A1," ","")で全角スペースを空文字に置換してからTRIMを適用してください。詳しくは「ノーブレークスペースの対処法」も参考になります。
大文字・小文字・全角・半角の変換
文字の変換系関数をまとめて紹介します。
| 関数 | 変換内容 | 数式の例 | 結果 | |
|---|---|---|---|---|
| [[2022-05-05-excel-function-howto-use-upper | UPPER]] | 全部大文字 | =UPPER("abc") | ABC |
| [[2022-05-07-excel-function-howto-use-lower | LOWER]] | 全部小文字 | =LOWER("ABC") | abc |
| [[2022-05-09-excel-function-howto-use-proper | PROPER]] | 先頭だけ大文字 | =PROPER("john smith") | John Smith |
| [[2022-09-02-excel-function-howto-use-asc | ASC]] | 全角→半角 | =ASC("ＡＢＣ") | ABC |
| JIS | 半角→全角 | =JIS("ABC") | ABC |
商品コードや顧客名の表記を統一するときに使います。EXACT関数で大文字小文字の一致を確認してから変換するのも効果的です。
新旧関数の対比表で文字列関数を整理
ここまで紹介した関数の中には、新旧の関係にあるものがあります。環境に合わせて選んでください。
| やりたいこと | 旧関数・旧手法 | 新関数（M365） | 新関数のメリット | ||
|---|---|---|---|---|---|
| 文字列の結合 | [[2022-06-30-excel-function-howto-use-concatenate | CONCATENATE]] | [[2022-07-24-excel-function-howto-use-concat | CONCAT]] | セル範囲を指定可能 |
| 区切り文字付き結合 | &で手動挿入 | [[2022-05-10-excel-function-howto-use-textjoin | TEXTJOIN]] | 空セル無視が可能 | |
| 区切り文字で分割 | 区切り位置（メニュー） | [[2022-12-22-excel-function-howto-use-textsplit | TEXTSPLIT]] | 数式で自動更新 | |
| 区切り文字の前を取得 | LEFT + FIND | [[2022-12-23-excel-function-howto-use-textbefore | TEXTBEFORE]] | 数式がシンプル | |
| 区切り文字の後を取得 | RIGHT + LEN + FIND | [[2022-12-24-excel-function-howto-use-textafter | TEXTAFTER]] | 数式がシンプル |
TIP
新関数が使えるかわからない場合は、セルに
=TEXTSPLIT("a,b",",")と入力して試してみてください。#NAME?エラーが出たらお使いのExcelでは未対応です。
文字列関数でよくあるエラーと対処法
文字列関数を使っていると遭遇しやすいエラーをまとめました。
| エラー | 原因 | 対処法 | |
|---|---|---|---|
#VALUE! | FIND/SEARCHで検索文字が見つからない | [[2022-01-16-excel-function-howto-use-if | IF関数]]やISERRORで「見つからない場合」の処理を追加する |
#NAME? | 関数名のスペルミス、または未対応バージョン | 関数名を確認する。TEXTSPLIT等はM365専用 | |
| 意図しない結合結果 | 数値がそのまま結合されて桁が変わる | [[2022-11-15-excel-function-howto-use-text | TEXT関数]]で書式指定してから結合する |
| スペースが残る | 全角スペースがTRIMで除去できない | =SUBSTITUTE(A1," ","") で全角スペースを先に除去 | |
| 分割結果がずれる | 区切り文字が想定と違う（全角カンマなど） | [[2022-03-21-excel-function-howto-use-len | LEN関数]]で文字数を確認し、区切り文字を特定する |
まとめ
Excelの文字列関数を4つのカテゴリで整理しました。
| カテゴリ | 主な関数 | 代表的な使い方 | ||
|---|---|---|---|---|
| 抽出 | LEFT, RIGHT, [[2023-01-02-excel-function-howto-use-mid | MID]], FIND | 氏名から姓・名を取り出す | |
| 結合 | [[2022-07-24-excel-function-howto-use-concat | CONCAT]], [[2022-05-10-excel-function-howto-use-textjoin | TEXTJOIN]], & | 名前と部署を結合する |
| 分割 | [[2022-12-22-excel-function-howto-use-textsplit | TEXTSPLIT]], LEFT+FIND | カンマ区切りデータを列に分ける | |
| 置換・整形 | [[2022-05-02-excel-function-howto-use-substitute | SUBSTITUTE]], [[2022-08-03-excel-function-howto-use-trim | TRIM]] | 住所から都道府県を除く |
迷ったら冒頭の逆引きインデックスに戻ってみてください。「やりたいこと」から関数を選ぶのが一番確実です。
M365環境であれば、TEXTSPLIT・TEXTBEFORE・TEXTAFTERといった新関数もぜひ試してみてください。数式がぐっとシンプルになりますよ。
