エクセルで文字列と数値が混在しているセルから数値だけを取り出したい場面はよくあります。
「ABC123」のような英数字混じりのデータや「商品コード：1234」のようなラベル付きデータから数値部分だけを抽出するには、関数を組み合わせたテクニックが必要です。
この記事では【Excel】エクセルで数値を抽出する方法（数値のみ抽出・文字列混じりのデータから取り出す関数）について解説していきます。
ポイントは
・数値の位置が不定の場合はFIND・LEN関数と組み合わせて抽出する
・TEXTJOIN・FILTER関数（Microsoft 365）を使うとより柔軟な抽出ができる
です。
それでは詳しく見ていきましょう。
エクセルで数値を抽出する方法1【LEFT・RIGHT・MID関数で固定位置から取り出す】
文字列の中で数値の位置が決まっている場合はLEFT関数・RIGHT関数・MID関数を使って数値を取り出す方法が手軽です。
LEFT関数は文字列の左端から指定した文字数を取り出し、RIGHT関数は右端から、MID関数は指定した位置から指定した文字数を取り出します。
サンプルデータで確認してみましょう
| 元データ | 形式 | 抽出したい数値 | 抽出結果 |
|---|---|---|---|
| 桜餅-1250 | 商品名-価格 | 1250 | |
| ID:4567 | ラベル:番号 | 4567 | |
| 20240401 | 8桁日付 | 2024（年） | |
| ABCー123ーXYZ | コード-番号-区分 | 123 |
1行目にヘッダーがあり、A列にさまざまな形式のデータが入力されています。
それぞれのパターンで数値部分だけを抽出する方法を解説していきます。
RIGHT関数で末尾の数値を取り出す
「桜餅-1250」のように末尾に数値がある場合はRIGHT関数で右端から4文字を取り出します。
この数式では「桜餅-1250」の右端4文字「1250」が文字列として返されます。
取り出した値を計算に使う数値として扱いたい場合はVALUE関数と組み合わせます。
FIND関数で区切り文字の位置を特定してMID関数で抽出する
「ID:4567」のように区切り文字（コロンなど）の後に数値がある場合はFIND関数で区切り文字の位置を特定してからMID関数で取り出します。
この数式ではFIND関数で「:」の位置を求め、その次の文字から文字列の末尾までを取り出しています。
「FIND(“:”,A3)」が「3」を返すため「MID(A3,4,LEN(A3)-3)」となり「4567」が抽出されます。
LEFT関数で先頭の数値を取り出す
「20240401」のような8桁の日付データから先頭4桁（年）を取り出すにはLEFT関数を使います。
この数式では左端4文字「2024」が数値として返されます。
エクセルで数値を抽出する方法2【数値の位置が不定の場合の抽出方法】
文字列の中に数値が含まれているが位置や桁数が一定でない場合は、MIN関数・FIND関数・IFERROR関数を組み合わせた数式で数値の開始位置を特定する方法が有効です。
この方法はやや複雑な数式になりますが、「ABC123」「XY45Z」「商品A678」のように数値の位置がバラバラなデータから数値を取り出すことができます。
MIN・FIND・IFERROR関数で数値開始位置を特定する
数値の開始位置を特定するには0から9の各数字がどの位置にあるかをFIND関数で調べ、その中の最小値（最初に数字が現れる位置）をMIN関数で取得します。
この数式では配列定数「{0,1,2,3,4,5,6,7,8,9}」を使って0〜9の各数字の位置をFIND関数で調べています。
数字が見つからない場合はIFERROR関数で「LEN(A2)+1」（文字列の長さ+1）を返してエラーを回避します。
MIN関数で最も小さい位置（最初に数字が現れる位置）を取得します。
この数値開始位置をもとにMID関数とLEN関数を組み合わせて数値部分を抽出します。
この数式は数値の開始位置から文字列の末尾まですべての文字を取り出します。
ただしこの方法では数値の後ろに文字がある場合（「ABC123XYZ」など）は数値以外の文字も含まれて返されるため、末尾の文字を除去する追加処理が必要になります。
エクセルで数値を抽出する方法3【SUBSTITUTE関数で文字列を除去して数値を取り出す】
「123円」「45kg」「￥6789」のように数値の前後に単位や記号が付いているデータから数値だけを取り出す場合は、SUBSTITUTE関数で不要な文字を削除してVALUE関数で数値化する方法が手軽です。
SUBSTITUTE関数は指定した文字列を別の文字列に置換する関数で、置換先を空白にすることで特定の文字を削除できます。
SUBSTITUTE関数で単位を除去して数値化する
たとえばA2セルに「1250円」が入力されている場合、「円」を除去して数値にするには以下のように入力します。
この数式ではSUBSTITUTE関数で「円」を空文字列に置換して「1250」という文字列にし、VALUE関数で数値に変換しています。
複数の不要な文字を一度に除去したい場合はSUBSTITUTE関数を入れ子にします。
この数式では「1,250円」から「円」とカンマを両方除去して「1250」という数値が返されます。
「￥」記号や「個」「kg」などの単位が付いたデータにも同様の方法が使えます。
除去したい文字の種類が多い場合はSUBSTITUTE関数を重ねる数は増えますが、基本的な考え方は同じです。
エクセルで数値を抽出する方法4【フラッシュフィルを使って素早く抽出する】
複雑な関数を使わずに数値を抽出したい場合は、フラッシュフィル機能を使う方法が手軽です。
フラッシュフィルはエクセルが入力パターンを自動認識して残りのセルに同じパターンを適用する機能で、Excel 2013以降で使用できます。
フラッシュフィルで数値を抽出する手順
抽出したい数値を最初のセルに手動で入力します。
たとえばA2セルに「桜餅-1250」がある場合、B2セルに「1250」と入力します。
B3セルに移動してCtrl＋Eを押すか「データ」タブの「フラッシュフィル」をクリックします。
エクセルがパターンを認識して残りのセルにも同じルールで数値を抽出して入力します。
フラッシュフィルは元データが変わっても自動更新されないため、データが確定している場合の一括変換に適しています。
パターンが正しく認識されない場合は最初の2〜3行に手動で入力してからフラッシュフィルを実行すると認識精度が上がります。
まとめ エクセルで文字列から数値のみを抽出（関数・複数条件・混在のデータ）
エクセルで数値を抽出する主な方法をまとめると、次のようになります。
・数値の位置が固定されている場合はLEFT・RIGHT・MID関数で直接取り出す
・区切り文字がある場合はFIND関数で位置を特定してMID関数と組み合わせる
・数値の位置が不定の場合はMIN・FIND・IFERROR関数の組み合わせで最初の数字位置を特定する
・単位や記号が付いている場合はSUBSTITUTE関数で除去してVALUE関数で数値化する
・関数を使わずに素早く抽出したい場合はフラッシュフィル（Ctrl＋E）が手軽
文字列混じりのデータから数値を抽出する作業は、外部システムからエクスポートしたデータの整理や、手入力で形式がばらばらになったデータの統一作業などで頻繁に発生します。
パターンが決まっているデータにはLEFT・RIGHT・MID関数を、パターンが不規則なデータにはフラッシュフィルをうまく使い分けることで作業効率が大幅に上がるでしょう。
状況に合った抽出方法を選んで、データ整理をスムーズに進めていきましょう。
コメント