Excelチュートリアル – 特定の位置に基づいてテキストまたは数値を抽出する

多くの場合、セル内の文や文字列から有用な内容のみを抽出する必要があるかもしれません。例えば、住所から都道府県を抽出したり、文からメールアドレスを抽出したり、会話から宅配伝票番号を抽出したりすることがあります。このチュートリアルでは、抽出をセル内の特定の位置に絞り込み、Excelで特定の位置に基づいてセルからテキストや数字を抽出するためのさまざまな方法をまとめています。

位置によるテキストの抽出

このセクションでは、セルからテキストを抽出できる一般的な位置をまとめ、それらを段階的に処理するための対応方法を提供します。詳細は参照してください。

1. 左または右から文字数を抽出する

文字列の左端または右端から文字数を抽出するには、以下の方法のいずれかを試すことができます。

1.1 数式を使用して最初または最後のN文字を抽出する

列Bに下のスクリーンショットに示すようにテキスト文字列リストがあるとします。各文字列から最初の2文字と最後の2文字を抽出するには、次の数式を適用できます。

テキスト文字列から最初のN文字を抽出する

LEFT関数を使用すると、Excelのテキスト文字列から最初のN文字を簡単に抽出できます。

一般的な数式

=LEFT(テキスト文字列,[文字数])

引数

これで、この数式を適用して、列Bのセルから最初の2文字を抽出できます。

1. 空のセルを選択し、以下の数式をコピーまたは入力して「Enter」キーを押して最初の結果を得ます。結果のセルを選択し、そのオートフィルハンドルを下にドラッグして、数式を他のセルに適用します。

=LEFT(B5,2)

これで、指定された範囲内の各セルから最初の2文字を抽出できました。

テキスト文字列から最後のN文字を抽出する

ここでは、Excelのテキスト文字列から最後のN文字を抽出するためにRIGHT関数を適用します。

一般的な数式

=RIGHT(テキスト文字列,[文字数])

引数

空白のセルを選択し、以下の数式をコピーまたは入力して「Enter」キーを押して結果を得ます。その後、この結果のセルを選択し、オートフィルハンドルを下にドラッグして他の結果を取得します。

=RIGHT(B5,2)

1.2 素晴らしいツールを使用して最初または最後のN文字を抽出する

上記の数式はシンプルですが、テキスト文字列の長いリストから最初または最後のn文字を抽出するには、上から下にオートフィルハンドルをドラッグする必要があり、少し時間がかかるかもしれません。ここで、「Kutools for Excel」の「Extract Text」機能をおすすめします。この機能を使用すると、テキスト文字列のリストから最初または最後のN文字を一括で抽出できます。

1. あらかじめテキストを抽出したい文字列リストを選択し、「Kutools」>「テキスト」>「テキストの抽出」をクリックします。

2. 表示された「テキストの抽出」ダイアログボックスで、次の設定を行う必要があります。

注: テキスト文字列が変更されたときに結果を動的にするには、「数式として挿入」ボックスをチェックできます。

3. 次に表示される「テキストの抽出」ダイアログボックスで、抽出された文字を出力するセルを選択し、「OK」をクリックします。

その後、選択されたセルから最初のN文字または最後のN文字が一括で抽出されます。

この機能についてさらに詳しく知るにはクリックしてください。

2. 特定の文字/単語の前または後ろのテキストを抽出

特定の文字または単語の前後からテキストを抽出するには、このセクションの異なるシナリオがご要望に応えます。

2.1 最初の区切り文字（特定の文字）の前または後のテキストを抽出する

以下のスクリーンショットに示すように、範囲 B4:B10 の各セルから最初の区切り文字の前または後のテキストを抽出するには、以下の方法のいずれかを適用できます。

2.1.1 最初の区切り文字の前のテキストを数式で抽出

LEFT関数とFIND関数に基づいた数式を適用すると、セル内の最初の区切り文字の前のテキストを抽出するのに役立ちます。以下の手順に従って操作を完了できます。

一般的な数式

=LEFT(テキスト文字列,FIND("区切り文字",テキスト文字列,1)-1)

引数

空白のセルを選択し、以下の数式をコピーまたは入力して「Enter」キーを押して最初の結果を得ます。最初の結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他のセルの最初の区切り文字の前のテキストを取得します。

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

2.1.2 最初の区切り文字の後のテキストを数式で抽出

以下の数式は、Excelのセルから最初の区切り文字の後のテキストを抽出するのに役立ちます。

一般的な数式

=MID(文字列,FIND("区切り文字",文字列)+1,LEN(文字列))

引数

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 最初の区切り文字の前後にあるテキストを驚きのツールで抽出

ここで、私たちは「Kutools for Excel」の「テキストの抽出」機能を強くお勧めします。この機能を使用すると、セル範囲から最初の区切り文字の前または後のテキストを一括で簡単に抽出できます。

1. テキストを抽出したいセルの範囲を選択し、次に「Kutools」>「テキスト」>「テキストの抽出」をクリックします。

2. 「テキストの抽出」ダイアログボックスで、次の通り設定する必要があります。

注: テキスト文字列が変更されたときに結果を動的にするには、「数式として挿入」ボックスをチェックできます。

3. 次に別の「テキストの抽出」ダイアログボックスが表示されます。結果を出力するセルを選択し、「OK」をクリックします。

その後、最初の区切り文字の前または後のテキストが選択されたセルから一度に抽出されます。

この機能についてさらに詳しく知りたい場合は、こちらをご覧ください: Excelでセルから特定のテキストを迅速に抽出する。

2.2 最後の区切り文字（文字）の前または後のテキストを抽出する

上記の手順では、セルから最初の区切り文字の前または後ろのテキストを抽出する方法について学びました。下のスクリーンショットに示すように、このセクションでは、セルから最後の区切り文字の前または後ろのテキストを抽出するための2つの数式を紹介します。以下の手順に従って操作を完了できます。

2.2.1 最後の区切り文字の前のテキストを数式で抽出

セル内の最後の区切り文字の前のテキストを抽出するには、LEFT 関数内で SEARCH、LEN、および SUBSTITUTE 関数を使用できます。

一般的な数式

=LEFT(テキスト文字列,SEARCH("#",SUBSTITUTE(テキスト文字列,"区切り文字","#",LEN(テキスト文字列)-LEN(SUBSTITUTE(テキスト文字列,"区切り文字",""))))-1)

引数

セルを選択し、以下の数式を入力して「Enter」キーを押して結果を得ます。この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、同じ列の他のテキスト文字列からテキストを抽出します。

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 数式を使用して最後の区切り文字の後のテキストを抽出する

セルから最後の区切り文字の前のテキストを抽出した後、必要に応じて以下の数式を適用して、最後の区切り文字の後のテキストを抽出できます。

一般的な数式

=RIGHT(テキスト文字列,LEN(テキスト文字列)-SEARCH("#",SUBSTITUTE(テキスト文字列,"区切り文字","#",LEN(テキスト文字列)-LEN(SUBSTITUTE(テキスト文字列,"区切り文字","")))))

引数

セルを選択し、以下の数式を入力して「Enter」キーを押して結果を得ます。この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、同じ列の他のテキスト文字列からテキストを抽出します。

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 N番目の文字以降のテキストを抽出

下の例を見てください。範囲 B4:B10 にテキスト文字列のリストがあります。各セルから3文字目以降のテキストを抽出するには、MID 関数と LEN 関数に基づいた数式を適用できます。

一般的な数式

=MID(文字列,n番目の文字+1,LEN(文字列))

引数

空白のセルを選択し、以下の数式をコピーまたは入力して「Enter」キーを押して結果を得ます。この結果セルを選択し、そのオートフィルハンドルを下にドラッグして他の結果を取得します。

=MID(B5,3+1,LEN(B5))

2.4 テキスト文字列からN番目の単語を抽出する

以下のスクリーンショットに示すように、テキスト文字列のリストがあり、その中からn番目の単語だけを抽出したい場合、このセクションではそれを実現するための3つの方法を提供します。

2.4.1 数式を使用してn番目の単語を抽出する

TRIM、MID、SUBSTITUTE、REPT、LEN 関数を組み合わせて使用すると、セル内のテキスト文字列からN番目の単語を抽出できます。

一般的な数式

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

引数

この場合、範囲 B5:B10 にはテキスト文字列が含まれ、D5:D10 には n 番目の単語を表す数字が含まれています。この数式を適用して、テキスト文字列から n 番目の単語を抽出しましょう。

空白のセルを選択し、以下の数式をコピーまたは入力して「Enter」キーを押して最初の結果を得ます。この結果のセルを選択し、そのオートフィルハンドルを下にドラッグして他のセルのn番目の単語を取得します。

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

注: 次のように、数式に直接n番目の数字を入力できます。

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 ユーザー定義関数を使用してN番目の単語を抽出する

上記の数式のほかに、ExcelでセルからN番目の単語を抽出するためにユーザー定義関数を適用することもできます。

1. 「Alt」キーと「F11」キーを押して、「Microsoft Visual Basic for Applications」ウィンドウを開きます。

2. 「Microsoft Visual Basic for Applications」ウィンドウで、「挿入」 > 「モジュール」をクリックし、以下のVBAコードをコードウィンドウにコピーしてください。

VBAコード: セル内のテキスト文字列からN番目の単語を抽出する

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. 「Alt」キーと「Q」キーを押して、「Microsoft Visual Basic for Applications」ウィンドウを閉じます。

4. 抽出したいn番目の単語を含むテキスト文字列が入ったワークシートに戻ります。空白のセルを選択し、以下の数式をコピーまたは入力して、「Enter」キーを押してn番目の単語を取得します。

=FindWord(B5,D5)

または

=FindWord(B5,2)

注: 式の中で、D5はn番目の単語を表す数字が入力されているセルです。あるいは、セル参照を直接数字に置き換えることもできます。

5. 結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他のセルのテキスト文字列からN番目の単語を抽出します。

2.4.3 素晴らしいツールを使用してn番目の単語を抽出する

上記の数式やユーザー定義関数を手動で適用したくない場合、ここではKutools for Excelの「セルからN番目の単語を抽出する」機能をお勧めします。この機能を使用すると、クリック数回だけでセル内のテキスト文字列からN番目の単語を簡単に抽出できます。

1. 結果を配置するセルを選択し、「Kutools」>「Formula Helper」>「Text」>「Extract」「セル内のn番目の単語」をクリックします。スクリーンショットをご覧ください：

2. 「関数ヘルパー」ダイアログボックスで、次の通り設定する必要があります。

3. そして、セル B5 のテキスト文字列から nth（2番目）の単語が抽出され、同時に数式が作成されていることが確認できます。この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他のテキスト文字列から nth 単語を取得します。

この機能についてさらに詳しく知るには、クリックしてください。

2.5 区切り文字のN番目の出現前または後のテキストを抽出

以下のスクリーンショットに示すように、テキスト文字列のリストがあると仮定します。2回目に出現するスペースの前または後のテキストを抽出するには、このセクションではそれを実行するのに役立つ2つの数式を提供します。

2.5.1 区切り文字のn番目の出現前にあるテキストを抽出

Excelのセルから、区切り文字のn番目の出現前にあるテキストを抽出するには、LEFT関数をSUBSTITUTE関数およびFIND関数と一緒に使用できます。

一般的な数式

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

引数

セルを選択し、以下の数式をコピーまたは入力して「Enter」キーを押して結果を得ます。この結果セルを選択し、オートフィルハンドルを下にドラッグしてリスト内の他の結果を取得します。

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

注: 式の中で、B5はテキストを抽出したい文字列が含まれているセルです。「 」はスペースを表し、数字の2は2回目のスペースの出現を示します。必要に応じてこれらを変更できます。

2.5.2 区切り文字のn番目の出現後のテキストを抽出

指定された区切り文字のn番目の出現後にテキストを抽出するには、RIGHT関数をSUBSTITUTE、LEN、およびFIND関数と共に使用できます。

一般的な数式

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

引数

これで、範囲 B5:B10 の各セルから2回目のスペースの後に続くテキストを抽出するこの数式を適用できます。以下の通りです。

セルを選択し、以下の数式を入力して「Enter」キーを押して結果を得ます。この結果セルを選択し、オートフィルハンドルを下にドラッグして他の結果を取得します。

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 改行の前または後のテキストを抽出

列Bに注文リストがあり、各セルから日付部分と製品番号部分だけを抽出したいとします。これを行うには、以下のExcel数式を使用できます。

2.6.1 最初の改行前のテキストを数式で抽出

上記のスクリーンショットでわかるように、日付部分はセル内の最初の改行の前にあります。このセクションでは、LEFT 関数と SEARCH 関数を組み合わせて使用し、セル内の最初の改行より前のテキストを抽出する方法を紹介します。

一般的な数式

=LEFT(セル, SEARCH(CHAR(10), セル)-1)

引数

空白のセルを選択し、以下の数式をコピーまたは入力して「Enter」キーを押して結果を得ます。この結果のセルを選択し、オートフィルハンドルを下にドラッグして他のセルにもこの数式を適用します。

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

そして、範囲 B5:B8 の各セルで最初の改行前のテキストが、下のスクリーンショットに示すように抽出されます。

注: 式の中で、CHAR(10) は Windows での改行を表します。

2.6.2 最後の改行後に数式を使用してテキストを抽出する

前のステップでは、セル内の最初の改行の前にあるテキストを抽出する方法について説明しました。そして、このパートでは、異なる数式を使用して、セル内の最後の改行の後に続くテキストを抽出する方法をガイドします。

一般的な数式

=TRIM(RIGHT(SUBSTITUTE(セル,CHAR(10),REPT(" ",200)),200))

引数

空白のセルを選択し、以下の数式を入力して「Enter」キーを押して結果を得ます。この結果セルを選択して、オートフィルハンドルを下にドラッグし、数式を他のセルにも適用します。

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

その後、リスト内の各セルの製品番号部分が、上記のスクリーンショットに示されているように抽出されます。

注: 式の中で、CHAR(10) は Windows での改行を表します。

  2.7 単語の前または後のテキストを抽出

以前のパートでは、特定の文字や区切り文字の前後からテキストを抽出する方法を学びました。では、単語全体の前または後ろからテキストを抽出するにはどうすればよいでしょうか？このセクションでは、このタスクを達成するための3つの方法を紹介します。

2.7.1 特定の単語の前にあるテキストを数式で抽出する

次の数式は、Excelのセル内の特定の単語の前にあるテキストを抽出するのに役立ちます。

一般的な数式

=IFERROR(LEFT(セル,FIND(単語,セル)-1),セル)

引数

空白のセルを選択し、以下の数式を入力して「Enter」キーを押して結果を得ます。この結果のセルを選択してから、オートフィルハンドルを下にドラッグして他のセルにもこの数式を適用します。

この例では、「Excel」という単語の前にあるすべてのテキストを抽出します。そのため、数式に直接その単語を入力し、二重引用符で囲みます。または、「Excel」という単語が含まれているセルを参照することもできます。

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

注記：

2.7.2 特定の単語の後のテキストを数式で抽出する

特定の単語の後に続くテキストを抽出するには、次の数式を適用して処理を完了できます。

一般的な数式

=TRIM(MID(セル,SEARCH(単語,セル)+LEN(単語),255))

引数

セルを選択し、以下の数式を入力して「Enter」キーを押して結果を得ます。結果のセルを選択し、オートフィルハンドルを下にドラッグして他のセルにもこの数式を適用します。

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

その後、各セル内の「Excel」という単語の後のすべてのテキストが、下のスクリーンショットに示すように抽出されていることが確認できます。

注記：

2.7.3 特定の単語の前後にあるテキストを驚くべきツールで抽出

数式を使用すると多くの不便が生じる可能性がある場合、ここでは「Kutools for Excel」の「テキストの抽出」機能を強くお勧めします。この機能は、Excelでの抽出作業を数回のクリックだけで自動化するのに役立ちます。

1. 「Kutools」>「テキスト」>「テキストの抽出」をクリックして、この機能を有効化します。

2. 「テキストの抽出」ダイアログボックスで、次の設定を行います。

2.1) 「位置による抽出」タブにいることを確認してください；

2.2) 「範囲」ボックスで、クリックしてください テキストを抽出したいセルの範囲を選択するボタン。

2.3) 「オプション」セクションで：

単語の前にあるすべてのテキストを抽出するには、「テキストの前」のラジオボタンを選択し、その後、その単語をテキストボックスに入力します。

特定の単語以降のすべてのテキストを抽出するには、「テキストの後」ラジオボタンを選択し、その後にその単語をテキストボックスに入力してください。

2.4) 「OK」ボタンをクリックします。スクリーンショットをご覧ください：

注: 動的な結果を作成したい場合は、「数式として挿入」ボックスをチェックしてください。そうすると、範囲内のデータが変更されたときに結果が自動的に更新されます。

3. 次に「テキストの抽出」ダイアログボックスが表示されます。結果を出力するセルを選択してから「OK」ボタンをクリックする必要があります。

選択範囲内の各セルにある特定の単語の前または後のテキストは、直ちに抽出されます。

注: この機能は大文字と小文字を区別します。

この機能についてさらに詳しく知るにはクリックしてください。

3. 文字/単語間の抽出

特定の文字や単語間のテキストを抽出したい場合は、次の方法をお試しください。

3.1 2つの文字間のテキストを抽出

2つの文字（同じ文字または異なる文字）の間のテキストを抽出するには、このセクションではいくつかの方法を提供しており、ニーズに合ったものを選択できます。

 3.1.1 数式を使用して2つの同じ文字間のテキストを抽出する

以下のスクリーンショットに示すように、列Bにテキスト文字列のリストがあり、範囲内の各セルから「/」の間の数字部分を抽出したい場合、次の数式が役立ちます。

空白のセルを選択し、以下の数式をコピーまたは入力して、「Enter」キーを押して結果を得ます。結果のセルを選択後、オートフィルハンドルを下にドラッグしてリスト内の他のセルの結果も取得します。

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

そして、範囲内の各セルから同じ文字「/」の間のテキストが抽出されます。 スクリーンショットをご覧ください：

注:

 3.1.2 二つの異なる文字間のテキストを数式で抽出する

セル内の2つの同じ文字の間からテキストを抽出する方法を学んだ後、ここでは2つの異なる文字の間からテキストを抽出するための数式を紹介します。以下のスクリーンショットに示すように、列Bの各セルから「<」と「>」の間にあるメールアドレスのみを抽出するには、次の手順を実行できます。

一般的な数式

=MID(LEFT(セル,FIND("end_char",セル)-1),FIND("start_char",セル)+1,LEN(セル))

引数

空白のセルを選択し、以下の数式をコピーまたは入力して「Enter」キーを押して結果を得ます。この結果のセルを選択してから、オートフィルハンドルを下にドラッグして他のセルにもこの数式を適用します。

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

指定された文字間のテキストのみが、上記のスクリーンショットに示されているように抽出されていることがわかります。

3.1.3 素晴らしいツールを使用して2つの文字間のテキストを抽出

ここで、Excelのセル内にある同じまたは異なる2つの文字列の間のテキストを簡単に抽出するのに役立つ「Kutools for Excel」の「指定されたテキスト間の文字列を抽出する」機能を強くお勧めします。

1. 結果を出力するための空白セルを選択し、次に「Kutools」>「Formula Helper」>「Formula Helper」をクリックします。

2. 「関数ヘルパー」ダイアログボックスで、次の設定を行います。

3. その後、セル B5 内の「<」と「>」の間にあるテキストのみが抽出されます。同時に、数式が作成され、この結果セルを選択して、オートフィルハンドルを下にドラッグすることで、同じリスト内の他のセルからテキストを抽出できます。

この機能についてさらに詳しく知るにはクリックしてください。

3.1.4 ルールに基づいて2つの文字間のテキスト（文字を含む）を抽出

2文字を抽出後に保持したい場合は、Kutools for Excelの「テキストの抽出」機能でルールを適用してみてください。

1. 「Kutools」>「テキスト」>「テキストの抽出」をクリックします。

2. 「テキストの抽出」ダイアログボックスで、次の設定を行います。

2.1) 「ルールによる抽出」タブをクリックします。

2.2) 「範囲」セクションで、クリックしてください 範囲内のセルを選択するためのボタンをクリックし、文字間のテキストを抽出したい場所を指定します。

2.3) 「テキスト」ボックスに「<*>」と入力します。

ヒント: "<" と ">" は、その間にテキストを抽出したい文字です。* は任意の数の文字を表すワイルドカードです。必要に応じて条件を入力できます。

2.4) 「追加」ボタンをクリックして、条件を「ルールの内容」リストボックスに追加します。

2.5) 「OK」ボタンをクリックします。スクリーンショットをご覧ください：

3. 別の「テキストの抽出」ダイアログボックスが表示されます。結果を出力するセルを選択し、「OK」ボタンをクリックしてください。

その後、指定された文字間のテキスト（文字を含む）が、選択範囲内の各セルから一括で抽出されます。

3.2 2つの単語間のテキストを抽出

2つの文字の間からテキストを抽出するだけでなく、2つの単語の間からテキストを抽出する必要が出てくる場合もあります。例えば、下のスクリーンショットに示すように、列Bの各セルから「KTE」と「feature」という2つの単語の間にあるすべてのテキスト文字列を抽出したいとします。この作業を完了するために、次のいずれかの方法を試してみてください。

3.2.1 数式を使用して2つの単語間のテキストを抽出する

セル内の2つの単語間のすべてのテキスト文字列を抽出するには、MID関数とSEARCH関数に基づいた数式を使用できます。

一般的な数式

=MID(セル,SEARCH("開始文字",セル)+3,SEARCH("終了文字",セル)-SEARCH("開始文字",セル)-4)

引数

空白のセルを選択し、以下の数式をコピーまたは入力して「Enter」キーを押して結果を得ます。この結果のセルを選択してから、オートフィルハンドルを下にドラッグして他のセルにもこの数式を適用します。

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

注: 数式では、数字の3は「KTE」という単語の文字数を表します。数字の4は、「KTE」という単語の文字数に1を加えたものを表します。

指定された2つの単語間のすべてのテキスト文字列が、列Bの各セルから抽出されていることがわかります。

3.2.2 素晴らしいツールを使用して2つの単語間のテキストを抽出する

多くのExcelユーザーにとって、数式は覚えにくく、扱いにくいものかもしれません。ここで、「Kutools for Excel」の「指定されたテキスト間の文字列を抽出する」機能を使用すれば、数回のクリックだけで2つの単語間のテキストを簡単に抽出できます。

1. 結果を出力するセルを選択し、次に「Kutools」>「Formula Helper」>「Formula Helper」をクリックします。

2. 「関数ヘルパー」ダイアログボックスで、次の通り設定する必要があります。

3. その後、セル B5 内の「KTE」と「feature」という2つの単語間にあるすべてのテキスト文字列が抽出されます。同時に、数式が作成され、この結果のセルを選択して、オートフィルハンドルを下にドラッグすることで、同じリスト内の他のセルからテキストを抽出できます。

位置による数値の抽出

英数字列のリストには、次の3つのケースが考えられます。

1. 数字はテキストの先頭にあります。
2. 数値はテキストの末尾にあります。
3. 数字はテキスト内のどこにでもある可能性があります。

このセクションでは、前述の各ケースで使用できる数値を抽出するためのさまざまな方法を提供します。

文字列の左から数字を抽出

この部分では、セル内のテキストの前にある数字のみを抽出するのに役立つ数式を紹介します。

一般的な数式

=LEFT(セル, MATCH(FALSE, ISNUMBER(MID(セル, ROW(INDIRECT("1:"&LEN(セル)+1)), 1) *1), 0) -1)

引数

注:

空白のセルを選択し、以下の数式を入力して「Ctrl」＋「Shift」＋「Enter」キーまたは「Enter」キーを押して結果を得ます。この結果のセルを選択し、オートフィルハンドルを下にドラッグして他のセルの数字を取得します。

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

注記：

右側の文字列から数値を抽出

以下のスクリーンショットに示すように、セル内のテキストの後に表示される数字のみを抽出するには、次の数式をお試しください。

一般的な数式

=RIGHT(セル, LEN(セル) - MAX(IF(ISNUMBER(MID(セル, ROW(INDIRECT("1:"&LEN(セル))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(セル))), 0)))

引数

注：

空白のセルを選択し、以下の数式を入力して「Ctrl」＋「Shift」＋「Enter」キーまたは「Enter」キーを押して結果を得ます。この結果セルを選択して、オートフィルハンドルを下にドラッグして他のセルの数字を取得します。

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

注記:

3. テキスト文字列内の任意の位置からすべての数字を抽出する

上記の方法は、テキスト文字列の左または右から数字のみを抽出するのに役立ちます。テキスト文字列内のどこにあってもすべての数字を抽出したい場合は、ここで3つの方法を提供します。

3.1 数式を使用して文字列内のすべての数字を抽出する

次の数式を適用して、Excelのテキスト文字列内の任意の場所からすべての数字を抽出できます。

1. 空のセルを選択し、以下の数式をコピーまたは入力して、「Enter」キーを押してセル B5 からすべての数字を取得します。

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. 結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他のすべてのセルの数字を取得します。

3.2 VBAを使用して文字列内のすべての数字を抽出する

上記の数式は、多くのExcelユーザーにとって長く複雑すぎます。実際には、VBAスクリプトを実行してExcelでのタスクを自動化することができます。以下のように操作できます。

1. 「Alt」キーと「F11」キーを押して、「Microsoft Visual Basic for Applications」ウィンドウを開きます。

2. 開いた「Microsoft Visual Basic for Applications」ウィンドウで、「挿入」>「モジュール」をクリックします。その後、以下のVBAコードをモジュールのコードウィンドウにコピーしてください。

VBAコード: テキスト文字列内のすべての数値を抽出

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3. 「F5」キーを押してコードを実行します。開いた「KutoolsforExcel」ダイアログボックスで、各セルからすべての数字を抽出したいセルの範囲を選択し、「OK」ボタンをクリックします。

4. 次に、もう一つの「KutoolsforExcel」ダイアログボックスが表示されます。このダイアログボックスで、出力先のセルを選択し、「OK」をクリックします。

その後、選択範囲内の各セルからすべての数値が一括で抽出されます。

4. 特定のテキストの後の数字を抽出

以下のスクリーンショットに示すように、特定のテキスト「No.」の後に続く任意の数字を抽出するには、このセクションではそれを実行するための2つの方法を提供します。

4.1 特定のテキストの後の数値を数式で抽出する

Excelでセル内の特定のテキストの後に続く数字を抽出するには、次の数式を適用できます。

一般的な数式：

=LOOKUP(10^6,1*MID(セル,MIN(FIND({0,1,2,3,4,5,6,7,8,9},セル&"0123456789",FIND("テキスト"," "&セル&" "))),{2,3,4,5,6}))

引数

空白のセルを選択し、以下の数式をコピーまたは入力して「Enter」キーを押して結果を得ます。この結果のセルを選択後、オートフィルハンドルを下にドラッグして他のセルにもこの数式を適用します。

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

注記：

4.2 ユーザー定義関数を使用して特定のテキストの後の数字を抽出する

次のユーザー定義関数を使用して、セル内の特定のテキストの後に続く数字を抽出することもできます。以下のように操作してください。

1. 「Alt」キーと「F11」キーを押して、「Microsoft Visual Basic for Applications」ウィンドウを開きます。

2. 「Microsoft Visual Basic for Applications」ウィンドウで、「挿入」>「モジュール」をクリックし、以下のVBAコードをモジュールコードウィンドウにコピーしてください。

VBAコード: セル内の特定のテキストの後の数字を抽出する

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3. 「Alt」キーと「Q」キーを押して、「Microsoft Visual Basic for Applications」ウィンドウを閉じます。

4. セルを選択し、以下の数式を入力して「Enter」キーを押します。この結果のセルを選択し、オートフィルハンドルを下にドラッグして他のセルにもこの数式を適用します。

=GetNumberAfterTheChar(B5,"No. ")

注記：

関連記事: