Excelで姓と名を分ける方法: TEXTSPLIT関数と数式

=TEXTBEFORE(A2," ")は最初のスペースより前をすべて返すので、Ana SilvaからはAnaが得られます。=TEXTAFTER(A2," ")はスペースより後をすべて返し、Silvaになります。両方の数式を下にコピーすれば、すべての氏名が2つの列に分かれます。

数式リセット

B2fx

=TEXTBEFORE(A2," ")

	A	B	C
1	Full name	First	Last
2	Ana Silva	Ana	Silva
3	Ben Okafor	Ben	Okafor
4	Chen Wu	Chen	Wu
5	Dara Kelly	Dara	Kelly
6	Eli Novak	Eli	Novak

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

A列の名前を変えると、2つの列も変わります。TEXTBEFORE、TEXTAFTER、TEXTSPLITにはMicrosoft 365かExcel 2024が必要です。Excel 2021以前向けのLEFTとFINDを使う方法は、この下で説明しています。

TEXTSPLIT: セルを複数の列に分ける

=TEXTSPLIT(A2," ")はスペースのたびに文字列を切り、各部分をそれぞれのセルにスピルします。1つの数式で、部分の数だけ列が埋まります：

数式リセット

B2fx

=TEXTSPLIT(A2," ")

	A	B	C	D	E
1	Text
2	Ana Maria Silva	Ana	Maria	Silva
3	Paris, Lyon, Nice	Paris	Lyon	Nice

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

• 区切り文字は2文字以上でも構いません。", "はカンマとそれに続くスペースで分けるので、各部分の先頭にスペースが残りません。
• 結果を入れる場所を空けておいてください。右側のセルが空でないと、数式は代わりに#SPILL!を表示します。
• 分けた部分は数字も含めて常に文字列なので、SUMでは無視されます。数値が必要なときは、前にマイナス記号を2つ付けます：=--TEXTSPLIT(A2,",")。

3つ目の引数は行の区切り文字です。両方を指定すると、TEXTSPLITは1つのセルから小さな表を作ります。カンマで列を、セミコロンで行を分けます。

数式リセット

A4fx

=TEXTSPLIT(A2,",",";")

	A	B
1	Teams
2	Ana,Red;Ben,Blue;Chen,Green
3
4	Ana	Red
5	Ben	Blue
6	Chen	Green

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Excelで使えるほかの書き方を2つ、文字列で示します：

=TEXTSPLIT(A2,,", ")        one piece per row: the column delimiter is left out
=TEXTSPLIT(A2,{";",","})    several delimiters in braces: splits at ; or ,

1つ目は列の区切り文字を省いて1行に1つずつ並べるもので、excel, formulas, textを3つのセルの列にします。1つのセルに入力したリストを行に変える方法です。2つ目は中かっこで複数の区切り文字を指定するもので、red;green,blueをred、green、blueに分けます。

ミドルネームと「姓, 名」の形

TEXTAFTER(A2," ")は最初のスペースより後をすべて返すので、Ana Maria SilvaではMaria Silvaになります。最後の単語を取り出すには、何番目かの指定を-1にして末尾から数えます。Silva, Anaの形で書かれた名前は、代わりにカンマとスペースで分けます：

数式リセット

C2fx

=TEXTAFTER(A2," ",-1)

	A	B	C
1	Full name	First	Last
2	Ana Maria Silva	Ana	Silva
3	Ben Okafor	Ben	Okafor
4	Chen Li Wu	Chen	Wu
5	Cher	Cher
6
7	Last, First	First	Last
8	Silva, Ana	Ana	Silva

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

区切り文字がないと、TEXTBEFOREとTEXTAFTERは#N/Aを返します。Cherのような1語の名前にはスペースがありません。5行目では6つ目の引数で、代わりに返す値を指定しています。=TEXTBEFORE(A5," ",,,,A5)は名前全体を、TEXTAFTER(...,"")は空のセルを返します。

数式リセット

C2fx

	A	B	C
1	Email	User	Domain
2	ana.silva@coddy.tech	ana.silva

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: C2で、A2のメールアドレスのドメイン、つまり@より後のすべてを返してください。

答え合わせ答えを見る

古いExcelで名前を分ける

TEXTBEFOREとTEXTAFTERがないときは、FINDでスペースの位置を探し、LEFT、MID、RIGHTでその前後を切り取ります：

数式リセット

B2fx

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

	A	B	C
1	Full name	First	Last
2	Ana Silva	Ana	Silva
3	Ben Okafor	Ben	Okafor
4	Chen Wu	Chen	Wu

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

• FIND(" ",A2)はスペースの位置で、Ana Silvaでは4です。
• LEFT(A2,4-1)はその前の3文字を取り出します。
• MID(A2,4+1,100)はスペースの次から最大100文字を取り出します。100は単に「どの名前より長い」という意味で、=RIGHT(A2,LEN(A2)-FIND(" ",A2))なら正確な長さで同じ結果になります。

これらの数式はスペースがないと#VALUE!を返します。代わりにセル全体を返すには、IFERRORで囲みます：=IFERROR(LEFT(A2,FIND(" ",A2)-1),A2)。

数式リセット

B2fx

	A	B
1	Code	Prefix
2	KT-1043-BLUE

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: B2で、LEFTとFINDを使って、商品コードの最初のハイフンより前の部分を返してください。

答え合わせ答えを見る

区切り位置とフラッシュフィル

数式を使わずに列を一度だけ分けるには：

1. 氏名の列を選択します。区切り位置は右側の列を上書きするので、右側の列が空であることを確認してください。
2. データ > 区切り位置。
3. カンマやタブなどの区切り文字によってフィールドごとに区切られたデータを選んで「次へ」をクリックし、スペース(またはカンマ、あるいはその他に別の文字を入力)にチェックを入れて「次へ」をクリックします。
4. 最初の部分が氏名を置き換えます。氏名を残すには、表示先を右側の空のセルにしてから「完了」をクリックします。

パターンがあるならフラッシュフィルのほうが手早く済みます。Ana Silvaの隣にAnaと入力してEnterを押し、Ctrl+Eか データ > フラッシュフィル を使うと、Excelがほかの行の名を埋めます。どちらのツールも固定の値を書き込みます。後で名前が変わっても分割した結果は変わりません。数式なら変わります。