テキスト文字列から2 番目または n 番目のスペース以降または以前のテキストを削除する

下記スクリーンショットのように、2 番目または n 番目の特定の区切り文字（スペース、カンマなど）以降、または以前のテキストをすべて削除したい場合があります。本記事では、Excel でこのタスクを簡単に処理できる数式をいくつかご紹介します。

• テキスト文字列から2 番目または n 番目の特定の区切り文字（スペース、カンマなど）以降のテキストを削除する
• テキスト文字列から2 番目または n 番目の特定の区切り文字（スペース、カンマなど）以前のテキストを削除する

テキスト文字列から2 番目または n 番目の特定の区切り文字（スペース、カンマなど）以降のテキストを削除する

Excel では、2 番目または n 番目のスペース（または他の区切り文字）以降のテキストを削除する際に、LEFT 関数、SUBSTITUTE 関数、および FIND 関数を組み合わせて使用できます。一般的な構文は次のとおりです。

• cell：テキストを削除したいセルの参照、またはテキスト文字列です。
• N：テキストを削除する際の基準となる、n 番目の区切り文字を示します。

結果を表示したい空白セルに、以下の数式をコピーまたは入力してください。

次に、フィルハンドルを下方向にドラッグしてこの数式を適用したいセルまで範囲指定すると、2 番目のスペース以降のすべてのテキストが一括で削除されます（下記スクリーンショット参照）。

数式の説明：

1．SUBSTITUTE(A2,「 」,「#」,3)：この SUBSTITUTE 関数は、セル A2 内の3 番目のスペースを指定された文字「#」に置き換えます。その結果、「Tom Hill Houston#Texas US」となり、この文字列が LEFT 関数の text 引数として使用されます。

2．FIND(「#」,SUBSTITUTE(A2,「 」,「#」,2),1)-1：この数式の部分は、LEFT 関数の num_chars 引数として使用されます。

• SUBSTITUTE(A2,「 」,「#」,2)：SUBSTITUTE 関数を使って、セル A2 内の2 番目のスペースを指定された「#」文字に置き換えます。結果は次のとおりです。「Tom Hill#Houston Texas US」。
• FIND(「#」,SUBSTITUTE(A2,「 」,「#」,2),1)-1=FIND(「#」, 「Tom Hill#Houston Texas US」, [[PH_26]])-1：この FIND 関数は、SUBSTITUTE 関数が返したテキスト文字列の先頭から「#」の位置を検索します。その後、1 を引くことでスペースを除外し、結果として数値8 が得られます。

3．LEFT(SUBSTITUTE(A2,「 」,「#」,3),FIND(「#」,SUBSTITUTE(A2,「 」,「#」,2),1)-1)=LEFT(「Tom Hill Houston#Texas US」, [[PH_32]])： 最後に、この LEFT 関数はセル A2 のテキスト文字列の先頭から8 文字を抽出します。

注記：

1．テキスト文字列を区切る他の区切り文字がある場合は、スペース文字を必要な区切り文字に置き換えるだけです。

2．n 番目の特定の区切り文字以降のテキストを削除するには、n の値を目的の数に変更してください。たとえば、3 番目のスペース以降のテキストを削除したい場合は、次の数式をご利用ください。

テキスト文字列から2 番目または n 番目の特定の区切り文字（スペース、カンマなど）以前のテキストを削除する

2 番目または n 番目の特定の区切り文字より前のテキストを削除するには、RIGHT 関数、LEN 関数、FIND 関数、および SUBSTITUTE 関数が役立ちます。一般的な構文は次のとおりです。

• cell：テキストを削除したいセル参照またはテキスト文字列です。
• N：テキスト削除の基準となる n 番目の区切り文字を表します。

以下の数式を、空白セルにコピーまたは入力してください。

次に、フィルハンドルを下方向にドラッグしてこの数式を目的のセルに適用すれば、必要な結果が得られます。

数式の説明：

1．LEN(A2)-FIND(「#」,SUBSTITUTE(A2,「 」,「#」,2))：この数式の部分は、RIGHT 関数の num_chars 引数として使用されます。

• SUBSTITUTE(A2,「 」,「#」,2)：SUBSTITUTE 関数を使って、セル A2 内の2 番目のスペースを指定された「#」文字に置き換えます。結果は次のとおりです。「Tom Hill#Houston Texas US」。
• FIND(「#」,SUBSTITUTE(A2,「 」,「#」,2))=FIND(「#」, 「Tom Hill#Houston Texas US」 )：この FIND 関数は、SUBSTITUTE 関数が返したテキスト文字列内にある「#」の位置を特定します。その結果、数値9 が得られます。
• LEN(A2)-FIND(「#」,SUBSTITUTE(A2,「 」,「#」,2))=25-9：セル A2 の文字列全体の長さから、2 番目のスペースの位置を引くことで、その後ろに続く文字数を取得します。これにより、結果として16 という数値が得られます。

2．RIGHT(A2,LEN(A2)-FIND(「#」,SUBSTITUTE(A2,「 」,「#」,2)))=RIGHT(A2, 16)：この RIGHT 関数は、セル A2 内のテキストの末尾から指定された文字数を抽出する際に使われます。

注記：

1．テキスト文字列を区切る他の区切り文字がある場合は、スペース文字を目的の文字に置き換えるだけです。

2．n 番目の特定の区切り文字より前のテキストを削除するには、n の値を目的の数値に変更してください。たとえば、3 番目のスペースより前のテキストを削除するには、以下の数式を適用します。

使用された関連関数：

関連記事：