テキスト(文字列)から数字のみを抽出

Excel では、数式を使用して、テキストと数値を組み合わせた値から数値を抽出できます。次の例では、数式を使用して値「TPS1984」から 1984 を取得します。

テキストから数値を抽出する数式

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

このチュートリアルでは、この公式を詳しく理解します。そしてそれを理解するには、それをいくつかの部分に分割する必要があります。

1. まず、値の文字数を取得する LEN 関数があります。
   
2. その後、INDIRECT は 1 と 7 (LEN によって返される) を使用したセル参照を使用します。
   
3. 次に、ROW 関数は INDIRECT を使用し、1 で始まり 7 で終わる数値の配列を返します。
   
4. これで、MID はこの配列を使用し、参照したセルの値を含む新しい配列を返します。
   
5. 次に、配列に 1 を乗算する単純な乗算式があります。この単純な乗算により、 #VALUE が含まれる新しい配列が得られます。テキスト値と数値のエラーはそのまま残ります。
   
6. ここから、IFERROR 関数はエラー値を空の値に変換します。
   
7. 最後に、TEXTJOIN によって値が結合され、結果には数値のみが含まれます。
   

上記の数式は、Excel 2019 バージョン以降でのみ機能します。これは、以前のバージョンでは使用できなかった TEXTJOIN を使用しているためです。

数式の一部の値が必要な場合は、その部分を選択して F9 ショートカット キーを押します。

TEXTJOIN は、ワークシート内のセルを更新すると値を変更する揮発性関数です。これにより、ワークブックの速度が少し遅くなる可能性があります。

しかし、以前のバージョン (2007、2010、2013、2016) を使用するとどうなるでしょうか。

以前のバージョンを使用している場合は、別の式を使用する必要があります。このような式:

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

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

上記の 2 つの式は、さまざまなタイプの値で適切に機能します。以下の方法でテストしてみました。

上のセル A1 には、テキスト、数値、記号で構成される複合値があり、結果には数値のみが含まれています。