Excel 文字列から数字のみを抽出する方法

2020年6月16日

数字と数字以外が混在する文字列から数字のみを抽出する方法を紹介します。

よく見かける手法では、ある程度ルールのある文字列から抽出しますが

この記事ではどんなルールでも対応します。

(単純に数字以外を削除します)

また全角の数字は半角の数字に変換した上で残します。

文字列結果
A-00050005
090-1234-XXXX0901234
0あ1うえ56お7012567

赤字…全角から半角に変換

文字列から数字のみを抽出する方法

サンプルファイルを使用する場合

手間なく抽出したい場合のサンプルファイルを用意しています。

B列に文字列を入力するとC列に抽出結果が表示されます。

このファイルでは文字列が100文字までの制限です。

これ以上の文字列を処理する場合は100のCY列以降にも数式をコピーし

2行目に続く連番を設定した上でC列のCONCAT関数の範囲を広げてください。

数式を作成する場合

まずこのようなExcelを用意します。

抽出したい文字列(B列)と結果のセル(C列)と1からの連番(D~M列)…文字数を用意します。

サンプルでは13まで用意していますが、文字列の最も長い文字数まで用意してください。

(サンプルでは090-1234-XXXXが13文字)

サンプルシートの枠組み。抽出したい文字列(B列)と結果のセル(C列)と1からの連番(D~M列)

次に文字数1の下のセルに数式を設定します。

IFERROR関数NUMBERVALUE関数IF関数LEN関数MID関数を使用します。

=IFERROR(NUMBERVALUE(IF(文字数:行指定に$<=LEN(文字列:列指定に$),MID(文字列:列指定に$,文字数:行指定に$,1),"-")),"")
数式の図解

この数式を全文字数列の全行にコピーします。

数式を全文字数列-全行にコピーする図解

この時点で数字のみがセルに表示されます。

数字のみがセルに表示されるキャプチャ

これを行ごとにCONCAT関数を設定して文字列結合をすれば完成です。

CONCAT関数で文字列結合をして完了するキャプチャ

応用:文字数のセル指定を削除(COLUMN関数)

数式として分かりやすいので2行目でベタ指定をしましたが、

COLUMN関数で自動的に指定する方法もあります。

「文字列:列指定に$」のセル指定を

=COLUMN()-COLUMN(最初の数式のセル:行列$指定)+1

に変更すると2行目の入力に依存しない安全な数式になります。

文字数のセル指定をCOLUMN関数に変更した例