Excel で数式を使用して区切り記号でセルを分割する方法 (8 つの方法)

このチュートリアルでは、Excel の数式を使用して区切り記号でセルを分割する方法を紹介します。区切り文字は、テキスト文字列内のデータのチャンクを区切る文字です。この記事では、Excel で数式を使用して区切り記号でセルを分割するさまざまな方法を紹介します。

セッションを始める前に、今日のワークブックの例について知っておきましょう。

この例のベースは、学生関連のデータ (Name 、ID 、コース 、都市 ）。これらのデータを使用して、さまざまな状況で機能するさまざまな方法を示します。

すべてのメソッドの例は、別のシートに保存されます。

練習用ワークブック

以下のリンクからワークブックをダウンロードしてください。

Excel で数式を使用して区切り記号でセルを分割する 8 つの方法

状況によっては、Excel でセルを分割する必要がある場合があります。これらは、誰かがインターネット、データベース、または同僚から情報をコピーしたときに発生する可能性があります。名前全体があり、姓名に分けたい場合は、Excel でセルを分割する必要がある場合の簡単な例です。

1. Excel 文字列関数と SEARCH 関数を組み合わせて、ダッシュ/ハイフン区切り文字で区切られたテキスト

区切り文字で分割するために最初に行う必要があるのは、区切り文字自体を見つけることです。区切り文字を見つけたら、区切り文字の両側から簡単に分割できます。 検索機能を使用します 区切り文字を見つけるには、LEFT を使用してテキストから値を抽出します 、中 、または正しい 関数。

1.1. LEFT と SEARCH 機能を統合

さぁ、始めよう。 左以来 関数には、テキストと文字数の 2 つのパラメーターがあります。テキスト値がわかっているので、テキストを挿入します。文字数については、SEARCH を使用します 関数。

手順:

• まず、セルを選択し、数式をそのセルに入力します。

=LEFT(B5, SEARCH("-",B5,1)-1)

• さらに、Enter キーを押します キーボードのキー

• フィル ハンドルをドラッグします アイコンを下にドラッグして、範囲全体で式を複製します。または、オートフィル 範囲、ダブルクリック プラス (+ ) 記号。

• ついに結果が表示されます。

🔎 数式の仕組み

この例では、区切り文字はハイフン「–」です。 」。 検索 関数は、ハイフンの位置を提供します。ここで、ハイフン自体は必要ありません。ハイフンの前に抽出する必要があります。

1.2. MID と SEARCH 関数を統合

では、中間値について書きましょう。このために、 MID を使用します &検索 機能。以下の手順に従いましょう。

手順:

• まず、セルを選択して次の式を入力してください。

=MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1)

• Enter を押します .

• 数式を範囲全体にコピーするには、フィル ハンドルをドラッグします。 シンボルを下向きにします。または、ダブルクリックすることもできます 加算 (+ ) オートフィルに署名

• 最後に、中間値がすべて分離されていることがわかります。

🔎 数式の仕組み

SEARCH によって、あるテキスト文字列が別の文字列内にある場所が返されます。 関数。ハイフンの次の文字から始まります。私たちが提供する文字数に基づいて、MID 指定した位置から開始して、テキスト文字列から特定の数の文字を取得します。

1.3.複合 RIGHT、LEN、および SEARCH 関数

ここで、最後のセルを区切るために、RIGHT の組み合わせを使用します 、レン 、検索 機能。数式の組み合わせを使用して、セルを区切り記号で分割する手順を見てみましょう。

手順:

• まず、セルを選択し、次の式をそのセルに挿入します。

=RIGHT(B5,LEN(B5) - SEARCH("-", B5, SEARCH("-", B5) + 1))

• Enter キーを押します もう一度キーボードのキーを押してください。

• その後、フィル ハンドルをドラッグします。 アイコンをクリックして、数式を範囲全体にコピーします。または、ダブルクリック プラス (+ ） サイン。これも式を複製します。

• したがって、最後の値は区切り記号で分割されます。

🔎 数式の仕組み

ここでは、LEN 関数は、最後のハイフンの位置を差し引いた文字列の全長を返します。 検索 関数は、ハイフンの位置を提供します。次に、違いは最後のハイフンの後の文字数と、RIGHT 関数はそれらを抽出します。

注: 同様の方法で、他の文字で列を分割できます。 「– 」を置き換えるだけです。 ' を必要な区切り文字に置き換えます。

続きを読む: カンマで文字列を分割する Excel 数式 (5 つの例)

2.改行でテキストを分割する数式を結合

文字列を改行で分割するには、前のセクションと同様の式を使用します。前の式に追加する必要がある関数がもう 1 つあります。関数は CHAR です .

2.1. LEFT、SEARCH、および CHAR 関数を組み合わせる

この CHAR 関数は改行文字を提供します。最初の値を取得してセルから分離するには、LEFT を使用します 、検索 、および CHAR 機能。この手順を見てみましょう。

手順:

• 前の方法と同様に、まず任意のセルを選択し、次の数式を入力して一番上の値を抽出します。

=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)

• Enter を押します 結果を見るための鍵

• さらに、プラス記号をドラッグして数式をコピーし、セル範囲の結果を取得できます。

🔎 数式の仕組み

10 ASCII です 行のコード。 10 を提供しています CHAR以内 改行を検索します。数字で決まる文字を返します。さらに休憩を探す。その後、これは最上位の値を返します。

2.2. MID、SEARCH、CHAR 関数を一緒に追加

中間値を分離するために、下のステップを見てみましょう。

手順:

• 他のアプローチと同様に、まず任意のセルを選択し、次の数式を入力して最大値を抽出します。

=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5, SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)

• 結果を表示するには、Enter キーを押します キー。

• さらに、数式を複製し、プラス をドラッグして、指定した範囲のセルの結果を取得することもできます。

2.3. RIGHT、LEN、CHAR、および SEARCH 関数に参加

テキストの右側では、式は RIGHT の組み合わせになります 、レン 、CHAR 、検索 機能。残りの値には適切な式を使用してください。したがって、下の値を分離するには、指示に従います。

手順:

• 前の手法と同様に、セルを選択し、次の式を入力してボトム値を抽出します。

=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1))

• Enter を押します キーボードのキー

• 最後に、加算記号をドラッグして、数式を複製し、指定した範囲のセルの答えを取得できます。

続きを読む: Excel でセルを分割する方法 (5 つの簡単なコツ)

3. Excel のテキストと数値文字列パターンでセルを分割

このセクションでは、文字列の後に数字が続くテキストを分割する方法を見ていきます。簡単にするために、シートにいくつかの変更を加えました (すべてのシートがワークブックに含まれる心配はありません)。この例では、生徒の名前があります とID 1 つの列にまとめて、2 つの異なる列に分割します。

3.1.組み合わせる RIGHT、SUM、LEN、および SUBSTITUTE 関数

SUBSTITUTE 内 数字をスペースに置き換え、 LEN を使用して数えています .テキストを分割し、その後に数値形式の文字列が続くようにするには、最初に数値を見つける必要があります。次に、その抽出された数値を使用してテキストを抽出できます。

手順:

• 最初に、結果を入れたいセルを選択します。この場合、セル C5 を選択します .
• 次に、数式をそのセルに挿入します。

=RIGHT(B5,SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},""))))

• Enter を押します キー。

• 加算記号をドラッグして、数式を複製し、セル範囲の答えを取得することもできます。

🔎 数式の仕組み

数字を抽出するには、0 から可能なすべての数字を探す必要があります 9まで 私たちの文字列内。次に、合計数を取得し、文字列の末尾からの文字数を返します。

3.2. LEFT と LEN の機能を統合

テキスト値を抽出するには、LEFT を使用する必要があります 関数と文字数のプレースホルダーで、その中の桁のセル長の合計の長さを提供します。そして、セル D5 から数字を取得します 、ID を分割するため

手順:

• 最初に、特定のセルを選択して数式を入力します。

=LEFT(B5,LEN(B5)-LEN(D5))

• Enter を押します .

• 加算記号をドラッグすると、数式を複製して、セルのグループの結果を取得できます。

続きを読む: Excel VBA:文字列を文字数で分割する (2 つの簡単な方法)

4.数式を使用して数値とテキスト文字列パターンでセルを分割

‘ を分割する方法を理解している場合 テキスト + 数 ' ということで、数値の文字列とそれに続くテキスト形式を分割する方法を想像し始めたことを願っています。アプローチは以前と同じですが、1 つだけ変更点があります。数字はテキストの左側にあるので、LEFT を使用する必要があります 関数を使用して数値を取得し、文字テキストの場合は RIGHT を使用します 関数。

4.1. LEFT、SUM、LEN、および SUBSTITUTE 関数のマージ

一番上の値の数値とテキスト文字列パターンでセルを分割するには、LEFT をマージする必要があります 、SUM 、レン、 とSUBSTITUTE 関数。

手順:

• まず、最初に特定のセルを選択し、そこに数式を入力します。

=LEFT(B5, SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, ""))))

• Enter を押します キー。

• さらに、加算記号をドラッグすると、数式を複製して、セルのグループの結果を取得できます。

4.2.複合 RIGHT &LEN 関数

正しいを組み合わせる必要があります とレン 最後の値の数値とテキスト文字列パターンでセルを分割する関数。

手順:

• まず、特定のセルを選択して数式を入力します。

=RIGHT(B5,LEN(B5)-LEN(C5))

• Enter キーを押します ボタン。

• さらに、加算記号をドラッグすることで、式を複製し、一連のセルの答えを得ることができます。

5. RIGHT、LEN、FIND、および SUBSTITUTE 関数を組み合わせてセルから日付を分割

テキストから日付を分割するには、RIGHT の組み合わせを使用できます 、レン 、検索 とSUBSTITUTE 関数。

手順:

• 目的のセルを選択し、そこに数式を入力します。

=RIGHT(B5,LEN(B5)-FIND(" ",SUBSTITUTE(B5," "," ",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-2)))

• さらに、Enter キーを押します キー。

• 加算記号をドラッグして、式を複製し、一連のセルの結果を取得することもできます。

🔎 数式の仕組み

日付の値は文字列の末尾にあるため、月、日、年を抽象化できるように多数のインスタンスをトラバースしました。ターゲット値を駆動するためにより多くのテキストが必要な場合は、インスタンス数を変更することでそれらを抽出できます。

注: この数式は、テキスト文字列の末尾に日付がある場合にのみ役立ちます。

続きを読む: Excel VBA:文字列をセルに分割 (4 つの便利なアプリケーション)

6. FILTERXML と SUBSTITUTE 関数を組み合わせてセルを分割する

提供された xpath を使用して、FILTERXML 関数 XML から特定のデータを抽出します ドキュメント。 FILTERXML を組み合わせることができます とSUBSTITUTE 細胞を分離する働きがあります。 Excel で数式を使用して区切り記号でセルを分割してみましょう。

手順:

• まず、目的のセルを選択して数式を入力します。

=FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")&"</s></t>","//s[2]")

• 次に、Enter を押します 結果を見る

• 最後に、加算記号をドラッグすると、数式を複製してセルのコレクションの結果を取得できます。

🔎 数式の仕組み

ここで、SUBSTITUTE テキスト文字列内の特定のテキストを置き換えることです。次に、Excel の FILTERXML 関数を使用すると、XML からデータを取得できます ファイル。

7. TEXTSPLIT 関数を適用してセルを区切り記号で区切る

TEXTSPLIT 関数を使用します ここで、列と行は区切り文字として使用され、テキスト シーケンスを分割します。行ごとまたは列ごとに分割できます。これは、任意のセルを区切り記号で分割する最短かつ簡単な方法です。 Excel で数式を使用して区切り記号でセルを分割するには、下の手順に従いましょう。

手順:

• 結果を表示したいセルを選択し、そこに数式を入力してください。

=TEXTSPLIT(B5,",")

• その後、Enter を押します .

• さらに、加算記号をドラッグして数式を複製し、一連のセルの結果を取得することもできます。

注: その横に十分な空の列があることを確認してください。そうしないと、#SPILL! に直面する可能性があります。 エラー。

8. TRIM、MID、SUBSTITUTE、REPT、LEN 関数を組み合わせてセルを分割

数式の別の組み合わせは TRIM です 、中 、置換 、REPT 、およびLEN 関数を使用すると、Excel の数式を使用して区切り記号でセルを分割できます。

手順:

• 数式を選択した後に結果を表示するセルに数式を入力します。

=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))

• 次に、Enter を押します .

• 足し算記号をスライドさせることで、数式を複製し、セル グループの結果を取得することもできます。

🔎 数式の仕組み

こちらレン テキスト文字列の長さを文字数で返します。次に、SUBSTITUTE 関数は、テキスト文字列の特定の場所に表示されるテキストを置き換えます。その後、 MID 関数は、指定した場所から始まる、テキスト文字列から特定の数の単語を返します。最後に、トリム 関数は、テキストからすべての空白を削除します。ただし、後書きのダブル スペースは除きます。

Excel でコマンドを使用して区切り記号でセルを分割する方法

Excel には、セルを分割する機能があります。 . データ タブのオプション内にあります。 . Excel で数式を使用して区切り記号でセルを分割する機能を使用するには、以下の手順に従う必要があります。

手順:

• まず、セルまたは列を選択します (多くの場合、列全体を選択する必要があります)。
• 次に、 データを調べます タブ . データ ツール内 セクションに、テキストから列へというオプションがあります .
• その後、それをクリックしてください。

• 目の前にダイアログ ボックスが表示されます。通常、区切り記号でセルを分割する必要があるため、Delimited を確認してください。 オプションを選択し、[次へ] をクリックします .

• 次に、いくつかの区切り記号を含むインターフェイスが表示されます。
• さらに、好みのものを選択するか、独自の区切り文字を入力することもできます。
• 区切り記号を選択すると、ボックスの下部に結果が表示されます。
• さらに、[次へ] をクリックします。

• この例では、値がカンマで区切られているため、ここでカンマを選択しています。
• 次へをクリックした後 値のタイプを選択するオプションが表示されます。[完了] をクリックします。 .別の値を取得します。

• 当分の間、これを一般のままにします (デフォルト) .下の画像に示すフォーマットは、いくつかのフォーメーションを作成した後に生成されました。

結論

上記の方法は、Excel で数式を使用して区切り記号でセルを分割するのに役立ちます。それが今日のすべてです。上記の方法で、Excel の数式を使用して区切り記号でセルを分割できます。数式を使用して区切り記号でセルを分割するいくつかの方法をリストしてみました。これが役立つことを願っています。分かりにくいところがあれば遠慮なくコメントください。ここで見逃した場合に備えて、他の方法でお知らせください。

参考文献

• Excel で 1 つのセルを 2 つに分割する方法 (5 つの便利な方法)
• Excel で 1 つのセルを半分に分割する方法 (斜めおよび水平方向)
• Excel で文字列を複数の列に分割する VBA (2 つの方法)
• Excel VBA:文字列を文字で分割 (6 つの便利な例)
• Excel で 1 つのセルに 2 つの線を作成する方法 (4 つの方法)