Excelで文字列から数値を抽出する（数式またはVBAを使用）-Ok Tech Masters

ツイートする

ピン

0 株式

Excelには、セル内の文字列から数値を抽出する組み込み関数はありません（その逆も同様です。数値部分を削除し、英数字文字列からテキスト部分を抽出します）。

ただし、これはExcel関数またはいくつかの単純なVBAコードを使用して実行できます。

まず、私が話していることをお話ししましょう。

以下のようなデータセットがあり、文字列から数値を抽出したいとします（以下のように）：

選択する方法は、使用しているExcelのバージョンによっても異なります。

2016より前のバージョンのExcelの場合、少し長い数式を使用する必要があります
Excel 2016では、新しく導入されたTEXTJOIN関数を使用できます
Excelのすべてのバージョンで使用可能なVBAメソッド

内容

1 Excelの文字列から数値を抽出する（Excel 2016の数式）
2 Excelの文字列から数値を抽出する（Excel 2013/2010/2007の場合）
3 VBAを使用してExcelでテキストと数字を分離する
- 3.1 Excelで文字列から数値を抽出する（VBAを使用）
- 3.2 Excelで文字列からテキストを抽出します（VBAを使用）
4 ああ、こんにちは、はじめまして。

Excelの文字列から数値を抽出する（Excel 2016の数式）

この数式は、新しく導入されたTEXTJOIN関数を使用するため、Excel2016でのみ機能します。

さらに、この数式では、テキスト文字列の最初、最後、または途中の数値を抽出できます。

このセクションで説明するTEXTJOIN数式は、すべての数字をまとめたものであることに注意してください。たとえば、テキストが「10チケットは$ 200」の場合、結果は10200になります。

次のようなデータセットがあり、各セルの文字列から数値を抽出するとします。

次の数式は、Excelの文字列の数値部分を示します。

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

これは配列数式なので、「 コントロール + シフト + エンター "Enterを使用する代わりに。

テキスト文字列に数値がない場合、この数式は空白（空の文字列）を返します。

関連する質問最高のマルウェア除去とランサムウェア保護

この式はどのように機能しますか？

この式を分解して、それがどのように機能するかを説明してみましょう。

ROW（INDIRECT（“ 1：”＆LEN（A2）））–数式のこの部分は、XNUMXから始まる一連の数値を示します。式のLEN関数は、文字列内の文字の総数を返します。在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
（MID（A2、ROW（INDIRECT（ "1："＆LEN（A2）））、1）* 1）–数式のこの部分は、＃VALUEの配列を返します。文字列ベースのエラーまたは数値。文字列内のすべてのテキスト文字は#VALUEになります！エラーとすべての値はそのままです。これは、MID関数に1を掛けたときに発生します。
IFERROR（（MID（A2、ROW（INDIRECT（ "1："＆LEN（A2）））、1）* 1）、 ""）– IFERROR関数を使用すると、すべての#VALUEが削除されます。エラー、数字のみが残ります。この部分の出力は次のようになります-{""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; " ";" ";" "; 1; 0; 0}
= TEXTJOIN（ ""、TRUE、IFERROR（（MID（A2、ROW（INDIRECT（ "1："＆LEN（A2）））、1）* 1）、 ""））– TEXTJOIN関数は文字列文字を単純に結合するようになりました（数字のみ）空の文字列を無視します。

プロのヒント：式の一部の出力を確認する場合は、セルを選択し、F2を押して編集モードに入り、出力する式の部分を選択して、F9を押します。結果はすぐに表示されます。次に、Control + Zを押すか、Escapeを押すことを忘れないでください。Enterキーを押さないでください。

同じロジックを使用して、英数字の文字列からテキストの一部を抽出することもできます。文字列からテキスト部分を取得する式は次のとおりです。

=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))

この式の小さな変更は、IF関数を使用して、MID関数から取得した配列にエラーがないかどうかをチェックすることです。エラーの場合は値を保持し、そうでない場合は空白に置き換えます。

次に、TEXTJOINを使用してすべてのテキスト文字を結合します。

注意：この式は有効ですが、揮発性関数（INDIRECT関数）を使用しています。これは、巨大なデータセットで使用している場合、結果が得られるまでに時間がかかる可能性があることを意味します。この数式をExcelで使用する前に、バックアップを作成することをお勧めします。

Excelの文字列から数値を抽出する（Excel 2013/2010/2007の場合）

Excel 2013. 2010.または2007を使用している場合、TEXTJOIN数式を使用できないため、これを行うには複雑な数式を使用する必要があります。

以下のようなデータセットがあり、各セルの文字列内のすべての数値を抽出するとします。

以下の式はこれを行います：

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")

テキスト文字列に数値がない場合、この数式は空白（空の文字列）を返します。

これは配列数式ですが、必要なし「Control-Shift-Enter」を使用して使用します。この式では、単純な入力が機能します。

この式のクレジットは驚くべきものになりますエクセルフォーラム。

この場合も、この数式は、位置に関係なく、文字列内のすべての数値を抽出します。たとえば、テキストが「10チケットは$ 200」の場合、結果は10200になります。

VBAを使用してExcelでテキストと数字を分離する

テキストと数字を頻繁に分離する（またはテキストから数字を抽出する）必要がある場合は、VBAメソッドを使用することもできます。

必要なのは、単純なVBAコードを使用してExcelでカスタムユーザー定義関数（UDF）を作成し、長くて複雑な数式の代わりにそのVBA数式を使用することだけです。

VBAでXNUMXつの数式を作成する方法を紹介します。XNUMXつは数値を抽出するためのもので、もうXNUMXつは文字列からテキストを抽出するためのものです。

Excelで文字列から数値を抽出する（VBAを使用）

このパートでは、文字列から数値部分のみを取得するカスタム関数を作成する方法を示します。

このカスタム関数を作成するために使用するVBAコードは次のとおりです。

関数GetNumeric（CellRef As String）Dim StringLength As Integer StringLength = Len（CellRef）For i = 1 To StringLength If IsNumeric（Mid（CellRef、i、1））Then Result = Result＆Mid（CellRef、i、1）Next i GetNumeric =結果終了関数

この関数を作成してワークシートで使用する手順は次のとおりです。

これで、ワークシートでGetText関数を使用できるようになります。コード自体ですべての面倒な作業を行ったので、必要なのは式= GetNumeric（A2）を使用することだけです。

これにより、文字列の数値部分がすぐにわかります。

ワークブックにはVBAコードが含まれているため、.xlsまたは.xlsm拡張子を付けて保存する必要があることに注意してください。

この数式を頻繁に使用する必要がある場合は、個人用マクロブックに保存することもできます。これにより、使用するすべてのExcelブックでこのカスタム数式を使用できるようになります。

Excelで文字列からテキストを抽出します（VBAを使用）

このパートでは、文字列からテキスト部分のみを取得するカスタム関数を作成する方法を示します。

このカスタム関数を作成するために使用するVBAコードは次のとおりです。

関数GetText（CellRef As String）Dim StringLength As Integer StringLength = Len（CellRef）For i = 1 To StringLength If Not（IsNumeric（Mid（CellRef、i、1）））Then Result = Result＆Mid（CellRef、i、1 ）Next i GetText = Result End Function

この関数を作成してワークシートで使用する手順は次のとおりです。

これで、ワークシートでGetNumeric関数を使用できるようになります。コード自体ですべての面倒な作業を行ったので、必要なのは式= GetText（A2）を使用することだけです。

これにより、文字列の数値部分がすぐにわかります。