Excelチュートリアル–特定の位置でテキストまたは数字を抽出します
多くの場合、アドレスからの州の抽出、文からの電子メールアドレスの抽出、会話からの宅配便番号の抽出など、文またはセル内のテキスト文字列から有用なコンテンツを抽出するだけで済みます。等々。 このチュートリアルでは、セル内の特定の位置に抽出を絞り込み、Excelの特定の位置によってセルからテキストまたは数値を抽出するのに役立つさまざまな方法を収集します。
目次: [ 隠れる ]
位置別にテキストを抽出する
このセクションでは、セルからテキストを抽出できる一般的な位置を収集し、それらを段階的に処理するための対応するメソッドを提供します。 詳細を参照できます。
1.左または右から文字数を抽出します
文字列の左側または右側から文字数を抽出するには、次のいずれかの方法を試してください。
次のスクリーンショットに示すように、列Bにテキスト文字列リストがあるとすると、各文字列から最初の2文字と最後の2文字を抽出するには、次の式を適用できます。
テキスト文字列から最初のN文字を抽出します
LEFT関数は、Excelのテキスト文字列から最初のN文字を簡単に抽出するのに役立ちます。
一般的な式
=LEFT(text_string,[num_chars])
Arguments
これで、この数式を適用して、列Bのセルから最初の2文字を抽出できます。
1.空白のセルを選択し、以下の数式をコピーまたは入力して、を押します。 入力します 最初の結果を得るためのキー。 結果セルを選択し、そのオートフィルハンドルを下にドラッグして、数式を他のセルに適用します。
=LEFT(B5,2)
これで、範囲B2:B5の各セルの最初の10文字が抽出されました。
テキスト文字列から最後のN文字を抽出します
ここでは、RIGHT関数を適用して、Excelのテキスト文字列から最後のN文字を抽出します。
一般的な式
=RIGHT(text_string,[num_chars])
Arguments
空白のセルを選択し、以下の数式をコピーまたは入力して、 入力します 結果を得るためのキー。 次に、この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他の結果を取得します。
=RIGHT(B5,2)
上記の式は単純ですが、テキスト文字列の長いリストから最初または最後のn文字を抽出するには、オートフィルハンドルを上から下にドラッグする必要があります。これには少し時間がかかる場合があります。 ここでお勧めします Kutools for Excelさん テキストを抽出 テキスト文字列のリストから最初または最後のN文字をまとめて抽出するのに役立つユーティリティ。
1.事前にテキストを抽出するテキスト文字列リストを選択し、をクリックします クツール > テキスト > テキストを抽出.
2.ポップアップで テキストを抽出 ダイアログボックスでは、次のように構成する必要があります。
Note:テキスト文字列が変更されたときに結果を動的にするために、次のことを確認できます。 数式として挿入 ボックス。
3.次のポップアップで テキストを抽出 ダイアログボックスで、抽出された文字を出力するセルを選択し、をクリックします OK.
次に、指定された最初または最後のN文字が、選択されたセルから一括で抽出されます。
このユーティリティの無料トライアル(30日)が必要な場合は、 クリックしてダウンロードしてください、次に、上記の手順に従って操作を適用します。
2.特定の文字/単語の前後にテキストを抽出します
特定の文字または単語の前後にテキストを抽出するには、このセクションのさまざまなシナリオでニーズを満たします。
以下のスクリーンショットに示すように、B4:B10の範囲の各セルから最初の区切り文字の前後のテキストを抽出するには、以下のいずれかの方法を適用できます。
2.1.1式を使用して最初の区切り文字の前にテキストを抽出します
LEFT関数とFIND関数に基づいて数式を適用すると、セルから最初の区切り文字の前にテキストを抽出するのに役立ちます。 以下の手順に従って実行できます。
一般的な式
=LEFT(text_string,FIND("delimiter",text_string,1)-1)
Arguments
空白のセルを選択し、以下の数式をコピーまたは入力して、を押します。 入力します 最初の結果を得るためのキー。 最初の結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他のセルの最初の区切り文字の前にテキストを取得します。
=LEFT(B5,FIND("-",B5,1)-1)
2.1.2式を使用して最初の区切り文字の後にテキストを抽出する
次の数式は、Excelのセルから最初の区切り文字の後にテキストを抽出するのに役立ちます。
一般的な式
=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))
Arguments
=MID(B5,FIND("-",B5)+1,LEN(B5))
2.1.3すばらしいツールを使用して、最初の区切り文字の前後にテキストを抽出します
ここでは強くお勧めします テキストを抽出 の有用性 Excel用のKutools。 この機能を使用すると、セルの範囲から最初の区切り文字の前後のテキストをまとめて簡単に抽出できます。
1.テキストを抽出するセルの範囲を選択し、[ クツール > テキスト > テキストを抽出.
2。 の中に テキストを抽出 ダイアログボックスでは、次のように構成する必要があります。
注: テキスト文字列が変更されたときに結果を動的にするには、[数式として挿入]チェックボックスをオンにします。
3.次に別の テキストを抽出 ダイアログボックスが表示され、結果を出力するセルを選択してクリックします [OK]をクリックします。
次に、最初の区切り文字の前後のテキストが、選択したセルから一度に抽出されます。
この機能の詳細については、次のWebサイトをご覧ください。 Excelのセルから特定のテキストをすばやく抽出する.
このユーティリティの無料トライアル(30日)が必要な場合は、 クリックしてダウンロードしてください、次に、上記の手順に従って操作を適用します。
上記の手順では、セルから最初の区切り文字の前後にテキストを抽出する方法を学習しました。 以下のスクリーンショットに示すように、このセクションでは、セルの最後の区切り文字の前後にテキストを抽出するためのXNUMXつの数式を示します。 以下の手順に従って実行できます。
2.2.1式を使用して最後の区切り文字の前にテキストを抽出します
セルから最後の区切り文字の前のテキストを抽出するには、LEFT関数内でSEARCH、LEN、およびSUBSTITUTE関数を使用できます。
一般的な式
=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)
Arguments
セルを選択し、以下の数式を入力して、 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、同じ列の他のテキスト文字列からテキストを抽出します。
=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)
2.2.2式を使用して最後の区切り文字の後にテキストを抽出します
セルから最後の区切り文字の前のテキストを抽出した後、必要に応じて、次の式を適用して最後の区切り文字の後のテキストを抽出できます。
一般的な式
=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))
Arguments
セルを選択し、以下の数式を入力して、 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、同じ列の他のテキスト文字列からテキストを抽出します。
=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))
以下の例を見てください。B4:B10の範囲のテキスト文字列のリストがあります。各セルからXNUMX番目の文字の後のテキストを抽出するには、MID関数とLEN関数に基づいて数式を適用できます。
一般的な式
=MID(text_string,nth_char+1,LEN(text_string))
Arguments
空白のセルを選択し、以下の数式をコピーまたは入力して、を押します。 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他の結果を取得します。
=MID(B5,3+1,LEN(B5))
以下のスクリーンショットに示すようなテキスト文字列のリストがあり、テキスト文字列からn番目の単語のみを抽出したい場合、このセクションでは、それを実行するためのXNUMXつの方法を提供します。
TRIM、MID、SUBSTITUTE、REPT、およびLEN関数を組み合わせて、セル内のテキスト文字列からn番目の単語を抽出できます。
一般的な式
=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))
Arguments
この場合、範囲B5:B10にはテキスト文字列が含まれ、D5:D10にはn番目の単語を表す数値が含まれます。この式を適用して、テキスト文字列からn番目の単語を抽出してみましょう。
空白のセルを選択し、以下の数式をコピーまたは入力して、を押します。 入力します 最初の結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他のセルの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)))
上記の数式とは別に、ユーザー定義関数を適用して、Excelのセルからn番目の単語を抽出することもできます。
1。 プレス 他の + F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。
2。 の中に アプリケーション向け Microsoft Visual Basic ウィンドウ、クリック インセット > モジュール、 次に、以下の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。 プレス 他の + Q を閉じるためのキー アプリケーション向け Microsoft Visual Basic 窓。
4.n番目の単語を抽出するテキスト文字列を含むワークシートに戻ります。 空白のセルを選択し、以下の数式をコピーまたは入力して、 入力します n番目の単語を取得するためのキー。
=FindWord(B5,D5)
Or
=FindWord(B5,2)
注: 数式で、D5はn番目の単語を表す数値を含むセルです。 または、セル参照を数値に直接置き換えることもできます。
5.結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他のセルのテキスト文字列からn番目の単語を抽出します。
上記の数式やユーザー定義関数を手動で適用したくない場合は、Kutools forExcelをお勧めします。 セル内のn番目の単語を抽出します 効用。 この機能を使用すると、数回クリックするだけで、セル内のテキスト文字列からn番目の単語を簡単に抽出できます。
1.結果を配置するセルを選択し、をクリックします クツール > フォーミュラヘルパー > テキスト > エキス セル内のn番目の単語。 スクリーンショットを参照してください:
2。 の中に 数式ヘルパー ダイアログボックスでは、次のように構成する必要があります。
3.次に、セルB5のテキスト文字列からn番目(XNUMX番目)の単語が抽出され、同時に数式が作成されていることがわかります。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他のテキスト文字列からn番目の単語を取得します。
このユーティリティの無料トライアル(30日)が必要な場合は、 クリックしてダウンロードしてください、次に、上記の手順に従って操作を適用します。
以下のスクリーンショットに示すようなテキスト文字列のリストがあるとします。 スペースのXNUMX番目の出現の前後にテキストを抽出するために、このセクションでは、それを実行するのに役立つXNUMXつの式を提供します。
2.5.1区切り文字がn番目に出現する前にテキストを抽出する
LEFT関数をSUBSTITUTE関数およびFIND関数と一緒に使用して、Excelのセルから区切り文字がn番目に出現する前にテキストを抽出できます。
一般的な式
=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)
Arguments
セルを選択し、以下の数式をコピーまたは入力して、を押します。 入力します 結果を得るためのキー。 この結果セルを選択し、オートフィルハンドルを下にドラッグして、リストに他の結果を表示します。
=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)
注: 数式で、B5は、テキストを抽出するテキスト文字列を含むセルです。 ここでの「」はスペースを表し、数字の2はスペースのXNUMX番目の出現を表します。 必要に応じて変更できます。
2.5.2区切り文字がn番目に出現した後にテキストを抽出する
区切り文字のn番目の出現後にテキストを抽出するには、SUBSTITUTE、LEN、およびFIND関数を使用してRIGHT関数を適用できます。
一般的な式
=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)
Arguments
これで、次のように、この数式を適用して、範囲B5:B10の各セルからスペースがXNUMX回出現した後のテキストを抽出できます。
セルを選択し、以下の数式を入力して、 入力します 結果を得るためのキー。 この結果セルを選択し、オートフィルハンドルを下にドラッグして、他の結果を取得します。
=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)
列Bに注文リストがあり、各セルから日付部分と製品番号部分のみを抽出するとします。 以下のExcelの数式を使用して実行できます。
上のスクリーンショットでわかるように、日付部分はセル内の最初の改行の前にあります。 このセクションでは、セル内の最初の改行の前にテキストを抽出するのに役立つSEARCH関数とともにLEFT関数を示します。
一般的な式
=LEFT(cell, SEARCH(CHAR(10), cell)-1)
Arguments
空白のセルを選択し、以下の数式をコピーまたは入力して、を押します。 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、この数式を他のセルに適用します。
=LEFT(B5, SEARCH(CHAR(10), B5)-1)
次に、下のスクリーンショットに示すように、範囲B5:B8の各セルの最初の改行が抽出される前のテキストを確認できます。
注: 式で、CHAR(10)はWindowsの改行を表します。
前のステップでは、セル内の最初の改行の前にテキストを抽出する方法について説明しました。 また、このパートでは、別の数式を使用してセル内の最後の改行の後にテキストを抽出する方法について説明します。
一般的な式
=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))
Arguments
空白のセルを選択し、以下の数式を入力して、 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、数式を他のセルに適用します。
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))
その後、製品番号。 上のスクリーンショットに示すように、リスト内の各セルの一部が抽出されます。
注: 式で、CHAR(10)はWindowsの改行を表します。
前のパートでは、文字または区切り文字の前後にテキストを抽出する方法を学習しました。 単語全体の前後にテキストを抽出するにはどうすればよいですか? このセクションでは、このタスクを実行するのに役立つXNUMXつの方法を紹介します。
次の数式は、Excelのセル内の特定の単語の前にテキストを抽出するのに役立ちます。
一般的な式
=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)
Arguments
空白のセルを選択し、以下の数式を入力して、 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、この数式を他のセルに適用します。
この例では、「Excel」という単語の前にあるすべてのテキストを抽出するため、数式に単語を直接入力し、二重引用符で囲みます。 または、「Excel」という単語を含むセルを参照することもできます。
=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)
注意:
特定の単語の後にテキストを抽出するには、次の式を適用してそれを実行できます。
一般的な式
=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))
Arguments
セルを選択し、以下の数式を入力して、 入力します 結果を得るためのキー。 結果のセルを選択し、そのオートフィルハンドルを下にドラッグして、この数式を他のセルに適用します。
=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))
次に、下のスクリーンショットに示すように、各セルの「Excel」という単語が抽出された後のすべてのテキストを表示できます。
注意:
2.7.3すばらしいツールを使用して、特定の単語の前後にテキストを抽出する
数式を使用すると多くの不便が生じる可能性があると思われる場合は、ここで強くお勧めします テキストを抽出 の有用性 Kutools for Excel。 この機能は、数回クリックするだけでExcelの抽出タスクを自動化するのに役立ちます。
1。 クリック クツール > テキスト > テキストを抽出 この機能を有効にします。
2。 の中に テキストを抽出 ダイアログボックスで、次の設定を行います。
注意: 動的な結果を作成する場合は、 数式として挿入 箱。 その後、範囲内のデータが変更されると、結果が自動的に更新されます。
3.次に、 テキストを抽出 ダイアログボックスが表示されたら、結果を出力するセルを選択して、[ OK
次に、選択した範囲の各セル内の特定の単語の前後のテキストがすぐに抽出されます。
注: この機能では大文字と小文字が区別されます。
このユーティリティの無料トライアル(30日)が必要な場合は、 クリックしてダウンロードしてください、次に、上記の手順に従って操作を適用します。
3.文字/単語間を抽出します
特定の文字または単語の間のテキストを抽出する場合は、次の方法を試してください。
XNUMXつの文字の間のテキストを抽出します。これは、同じ文字でも異なる文字でもかまいません。 このセクションではいくつかの方法を紹介しますが、必要に応じてそのうちのXNUMXつを選択できます。
3.1.1式を使用してXNUMXつの同じ文字間のテキストを抽出する
下のスクリーンショットに示すように、列Bにテキスト文字列のリストがあり、範囲内の各セルから文字「/」の間の数字部分を抽出する場合は、次の数式を使用すると便利です。
空白のセルを選択し、以下の数式をコピーまたは入力して、 入力します 結果を得るためのキー。 結果セルを選択し、オートフィルハンドルを下にドラッグして、リスト内の他のセルの結果を取得します。
=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")
次に、XNUMXつの同じ文字「/」の間のテキストが範囲内の各セルから抽出されます。 スクリーンショットを参照してください:
注:
3.1.2式を使用してXNUMXつの異なる文字間のテキストを抽出する
セル内のXNUMXつの同じ文字間でテキストを抽出する方法を学習した後、ここではXNUMXつの異なる文字間でテキストを抽出する式を示します。 下のスクリーンショットに示すように、列Bの各セルから「<」と「>」の間の電子メールアドレスのみを抽出するには、次のようにします。
一般的な式
=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))
Arguments
空白のセルを選択し、以下の数式をコピーまたは入力して、 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、この数式を他のセルに適用します。
=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))
上のスクリーンショットに示すように、指定した文字の間のテキストのみが抽出されていることがわかります。
3.1.3すばらしいツールを使用してXNUMX文字間のテキストを抽出する
ここでは強くお勧めします 指定されたテキスト間の文字列を抽出します の特徴 Kutools for Excel Excelのセル内のXNUMXつの同じ文字または異なる文字の間のテキストを簡単に抽出できるようにします。
1.空白のセルを選択して結果を出力し、[ クツール > フォーミュラヘルパー > フォーミュラヘルパー.
2。 の中に 数式ヘルパー ダイアログボックスで、次の設定を行います。
3.次に、セルB5内の「<」と「>」の間のテキストのみが抽出されます。 その間に、数式が作成されました。この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、同じリスト内の他のセルからテキストを抽出できます。
このユーティリティの無料トライアル(30日)が必要な場合は、 クリックしてダウンロードしてください、次に、上記の手順に従って操作を適用します。
3.1.4ルールによりXNUMX文字(文字を含む)間のテキストを抽出する
抽出後にXNUMX文字を保持したい場合は、Kutools forExcelのテキストの抽出機能でルールを適用してみてください。
1。 クリック クツール > テキスト > テキストを抽出.
2。 の中に テキストを抽出 ダイアログボックスで、次の設定を行います。
3.別の テキストを抽出 ダイアログボックスが表示されたら、結果を出力するセルを選択して、[ OK
次に、指定された文字(文字を含む)の間のテキストが、選択された範囲の各セルから一括で抽出されます。
このユーティリティの無料トライアル(30日)が必要な場合は、 クリックしてダウンロードしてください、次に、上記の手順に従って操作を適用します。
XNUMXつの文字の間のテキストを抽出することに加えて、XNUMXつの単語の間のテキストを抽出する必要がある場合もあります。 たとえば、次のスクリーンショットに示すように、列Bの各セルからXNUMXつの単語「KTE」と「feature」の間のすべてのテキスト文字列を抽出します。 次のいずれかの方法を試して、それを実行できます。
3.2.1数式を使用してXNUMXつの単語の間にテキストを抽出する
MID関数とSEARCH関数に基づく数式を使用して、セル内のXNUMXつの単語間のすべてのテキスト文字列を抽出できます。
一般的な式
=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)
Arguments
空白のセルを選択し、以下の数式をコピーまたは入力して、 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、この数式を他のセルに適用します。
=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)
Note:式では、数字の3は「KTE」という単語の文字の長さを表します。 数字の4は、「KTE」という単語の文字長に1を加えたものを表します。
指定されたXNUMXつの単語の間のすべてのテキスト文字列が列Bの各セルから抽出されていることがわかります。
3.2.2素晴らしいツールでXNUMXつの単語の間にテキストを抽出する
多くのExcelユーザーにとって、数式は覚えて処理するのが難しい場合があります。 ここで、 指定されたテキスト間の文字列を抽出します の特徴 Kutools for Excel、数回クリックするだけで、XNUMXつの単語間のテキストを簡単に抽出できます。
1.結果を出力するセルを選択し、をクリックします クツール > フォーミュラヘルパー > フォーミュラヘルパー。
2。 の中に フォーミュラヘルパー ダイアログボックスでは、次のように構成する必要があります。
3.次に、セルB5内のXNUMXつの単語「KTE」と「feature」の間のすべてのテキスト文字列が抽出されます。 その間に、数式が作成されました。この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、同じリスト内の他のセルからテキストを抽出できます。
このユーティリティの無料トライアル(30日)が必要な場合は、 クリックしてダウンロードしてください、次に、上記の手順に従って操作を適用します。
位置ごとに数字を抽出する
英数字の文字列のリストについては、次のXNUMXつの場合があります。
- 番号はテキストの冒頭にあります;
- 番号はテキストの最後にあります;
- 番号はテキストのどこにあってもかまいません.
このセクションでは、上記のそれぞれの場合に数値を抽出するために使用できるさまざまな方法を提供します。
1文字列の左側から数値を抽出します
このパートでは、セル内のテキストの前に表示される数値のみを抽出するのに役立つ数式を紹介します。
一般的な式
=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)
Arguments
注:
空白のセルを選択し、以下の数式を入力してを押します Ctrlキー + シフト + 入力します or 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他のセルの数を取得します。
=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)
注意:
2文字列の右側から数値を抽出します
下のスクリーンショットに示すように、セル内のテキストの後に表示される数字のみを抽出するには、次の数式を試してください。
一般的な式
=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))
Arguments
注:
空白のセルを選択し、以下の数式を入力してを押します Ctrlキー + シフト + 入力します or 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他のセルの数を取得します。
=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))
注意:
3.テキスト文字列の任意の位置からすべての数値を抽出します
上記の方法は、テキスト文字列の左または右からのみ数値を抽出するのに役立ちます。 テキスト文字列の任意の場所からすべての数値を抽出する場合は、ここでXNUMXつの方法を使用してそれを実行できます。
3.1数式を使用して、文字列内の任意の場所からすべての数値を抽出します
次の数式を適用して、Excelのテキスト文字列の任意の場所からすべての数値を抽出できます。
1.空白のセルを選択し、以下の数式をコピーまたは入力して、を押します。 入力します セル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。 プレス 他の + F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。
2.オープニングで アプリケーション向け Microsoft Visual Basic ウィンドウ、クリック インセット > モジュール。 次に、以下の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 コードを実行するためのキー。 オープニングで Kutools for Excel ダイアログボックスで、各セルからすべての数値を抽出するセルの範囲を選択し、[ OK
4.次に別の Kutools for Excel ダイアログボックスが表示されます。 このダイアログボックスで、宛先セルを選択してクリックします [OK]をクリックします。
次に、選択した範囲の各セルからすべての数値が一括で抽出されます。
4.特定のテキストの後に数字を抽出します
下のスクリーンショットに示されているように、特定のテキスト「いいえ」の後に数字を抽出するために、このセクションでは、それを実行するのに役立つXNUMXつの方法を提供します。
次の数式を適用して、Excelのセル内の特定のテキストの後に数値を抽出できます。
一般的な式:
=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))
Arguments
空白のセルを選択し、以下の数式をコピーまたは入力して、 入力します 結果を得るためのキー。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、この数式を他のセルに適用します。
=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。 プレス 他の + F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。
2。 の中に アプリケーション向け Microsoft Visual Basic ウィンドウ、クリック インセット > モジュール、 次に、以下の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。 プレス 他の + Q を閉じるためのキー アプリケーション向け Microsoft Visual Basic 窓。
4.セルを選択し、以下の数式を入力して、 入力します 鍵。 この結果セルを選択し、そのオートフィルハンドルを下にドラッグして、この数式を他のセルに適用します。
=GetNumberAfterTheChar(B5,"No. ")
注意:
関連記事:
Excelチュートリアル:テキスト、数値、および日付のセルを分割する(複数の列に分割)
このチュートリアルは、テキストセルの分割、数値セルの分割、日付セルの分割のXNUMXつの部分に分かれています。 各パートには、同じ問題が発生したときに分割ジョブを処理する方法を理解するのに役立つさまざまな例が用意されています。
詳細はクリックしてください...
Excelは、セルの指定された位置にテキストと数値を追加します
Excelでは、セルにテキストや数字を追加することは非常に一般的な作業です。 名前の間にスペースを追加する、セルにプレフィックスまたはサフィックスを追加する、ソーシャル番号にダッシュを追加するなど。 このチュートリアルでは、Excelでのシナリオの追加のほとんどすべてをリストし、対応する方法を提供します。
詳細はクリックしてください...
Excelは、テキスト文字列から文字、単語、数字を削除します
文字、数字、またはその他の特定の記号を含むテキスト文字列の長いリストがあるとします。 場合によっては、テキスト文字列から右、左、中央などの位置に基づいて一部の文字を削除したり、文字列のリストから不要な文字や数字を削除したりする必要があります。 解決策をXNUMXつずつ見つけることは頭痛の種になります。このチュートリアルでは、Excelで文字、単語、または数字を削除するためのあらゆる種類の方法を収集します。
詳細はクリックしてください...
最高のオフィス生産性向上ツール
🤖 | Kutools AI アシスタント: 以下に基づいてデータ分析に革命をもたらします。 インテリジェントな実行 | コードを生成 | カスタム数式の作成 | データを分析してグラフを生成する | Kutools関数を呼び出す... |
人気の機能: 重複を検索、強調表示、または識別する | 空白行を削除する | データを失わずに列またはセルを結合する | 数式なしのラウンド ... | |
スーパールックアップ: 複数の基準の VLookup | 複数の値の VLookup | 複数のシートにわたる VLookup | ファジールックアップ .... | |
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する | 依存関係のドロップダウン リスト | 複数選択のドロップダウンリスト .... | |
列マネージャー: 特定の数の列を追加する | 列の移動 | Toggle 非表示列の表示ステータス | 範囲と列の比較 ... | |
注目の機能: グリッドフォーカス | デザインビュー | ビッグフォーミュラバー | ワークブックとシートマネージャー | リソースライブラリ (自動テキスト) | 日付ピッカー | ワークシートを組み合わせる | セルの暗号化/復号化 | リストごとにメールを送信する | スーパーフィルター | 特殊フィルター (太字/斜体/取り消し線をフィルター...) ... | |
上位 15 のツールセット: 12 テキスト ツール (テキストを追加, 文字を削除する、...) | 50+ チャート 種類 (ガントチャート、...) | 40+ 実用的 式 (誕生日に基づいて年齢を計算する、...) | 19 挿入 ツール (QRコードを挿入, パスから画像を挿入、...) | 12 変換 ツール (数字から言葉へ, 通貨の換算、...) | 7 マージ&スプリット ツール (高度な結合行, 分割セル、...) | ... もっと |
Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。 最も必要な機能を入手するにはここをクリックしてください...
Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作業をはるかに簡単にします
- Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
- 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
- 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!