エクセル関数で、文字列から数字だけを抽出する方法を紹介しています。エクセルで、セルに文字列と数値が混在して入力されている場合、文字列の中から数字のみ取り出したいということがあります。そのような場合、どのようにしてセルの文字列から数字のみを取り出せばよいのでしょう。文字列の中に、数値が規則的に入力されていると簡単に抽出できますが、不規則で数字の数も一定ではないと、抽出するのは難しくなります。ここでは、セルの文字列から、数字がどの位置にあっても、数字の数が幾つあっても、数字だけを簡単に抽出する方法を説明していきます。文字列の左端、中、右端でも簡単に数値のみ抽出できます。
目次
数字＋文字列から数字を抽出する方法
数字＋文字列から数字を抽出します。
B1に、「325ANｱ 品川」の文字列から数字のみ抽出する式を入力しましょう。
数字が文字列の一番左端にある時、エクセル関数LEFTが基本となります。
LEFT関数にSUM関数、LEN関数、SUBSTITUTE関数を組み合わせて抽出します。
B1 =LEFT(A1,LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},))))
「325」が返されました！
文字列の左端から、数字のみ抽出されています。
B1の式は、エクセルLEFT関数で、左から何文字抽出するということがポイントになります。
=LEN(A1)*10
「325ANｱ 品川」の文字数は、「9」になります。上の式で、9＊10で「90」が返されます。
=LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},))
上の式で「9」が返されます。
=SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},)))
上の式で「87」が返されます。
全ての式を組み合わせると、エクセルLEFT関数で、左から抽出する文字数が算出されます。
セル内の文字列の中にある、数字の数です。
=LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},)))
上の式で、文字列の中にある数字の数「3」が返されます。
この式は覚えておいた方がよいでしょう。応用が利きます。
エクセルLEFT関数の「文字数」に、この式を入力します。
その式が、B1の式になります。
文字列＋数字＋文字列から数字を抽出する方法
文字列＋数字＋文字列から数字を抽出します。
エクセルMID関数で、数字を文字列の真ん中から抽出します。
B1に、「契約№は、WW2352です。」の文字列から、数字のみ抽出する式を入力しましょう。
数字が文字列の真ん中にある時、エクセル関数MIDが基本となります。
MID関数にMIN関数、FIND関数、SUM関数、LEN関数、SUBSTITUTE関数を組み合わせて抽出します。
B1 =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},))))
「2352」が返されました！
数字が文字列の真ん中から、抽出されています。
B1の式は、エクセルMID関数で数字が最初に現れる位置の番号と、その位置から何文字取り出すということがポイントになります。
数字が最初に現れる位置の番号を出す式は、下の式です。
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890))
上の式で「8」が返されます。
この式は、A1の文字列で、最初に現れる数字が左から何番目にあるかを調べています。
FIND関数で数字を検索します。検索の対象となる文字列をA1だけ指定すると、A1に全ての数字があるわけではないのでエラー「#VALUE!」になります。
エクセルFIND関数の式に「A1&1234567890」を指定します。
FIND関数の式にMIN関数を組み合わせると、A1の文字列で、最初に現れる数字が左から何番目なのか出せます。この式は応用できますので覚えておきましょう。
取り出す文字数を算出する式は下の式です。
「数字＋文字列から数字を抽出する方法」を参考にして下さい。
この式は文字列から数字だけを抽出する時、文字列の中にある数字の数を算出できます。
=LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},)))
上の式で「4」が返されます。
エクセルMID関数に、「数字が最初に現れる位置の番号を出す式」と「文字列の中にある数字の数を算出する式」を指定します。その式が、B1の式になります。
文字列＋数字から数字を抽出する方法
文字列＋数字から数字を抽出します。
B1に、「社員№：20136」の文字列から数字のみ抽出する式を入力しましょう。
数字が文字列の一番右端にある時、エクセル関数RIGHTが基本となります。
RIGHT関数にSUM関数、LEN関数、SUBSTITUTE関数を組み合わせて抽出します。
B1 =RIGHT(A1,LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},))))
「20136」が返されました！
文字列から、右端の数字のみ抽出されています。
B1の式は、エクセルRIGHT関数で、右から何文字取り出すということがポイントになります。
取り出す文字数を算出する式は下の式です。
「数字＋文字列から数字を抽出する方法」を参考にして下さい。文字列の中にある数字の数を算出できます。
=LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},)))
上の式で「5」が返されます。
RIGHT関数の「文字数」に、この式を入力します。その式が、B1の式です。
ここでは、LEFT関数、MID関数、RIGHT関数を中心に使って、セルの文字列から数字を抽出しました。
LEFT関数、MID関数、RIGHT関数は、どのような関数なのでしょうか。
基本的な使い方を、復習しましょう。
LEFT関数
文字列の左端から「4」文字、抽出します。
B2に、文字列の左から「4」文字、抽出する式を入力しましょう。
B2 =LEFT(A2,4)
「W102」が返されます。
B2の式を下にドラッグして、コピーしましょう。
B列に、A列の文字列の左端から「4」文字、抽出されました！
MID関数
文字列の左端５番目から「２」文字、抽出します。
B2に、文字列に左５番目から「２」文字、抽出する式を入力しましょう。
B2 =MID(A2,5,2)
「ab」が返されます。
B2の式を下にドラッグして、コピーしましょう。
B列に、A列の文字列の左５番目から「２」文字、抽出されました！
RIGHT関数
文字列の右端から「３」文字、抽出します。
B2に、文字列に右から「３」文字、抽出する式を入力しましょう。
B2 =RIGHT(A2,3)
「ab」が返されます。
B2の式を下にドラッグして、コピーしましょう。
B列に、A列の文字列の右端から「３」文字、抽出されました！
LEFT関数
| 書式 | 説明 |
| LEFT(文字列, [文字数]) | 文字列の先頭から指定された数の文字を返します。 |
SUM関数
| 書式 | 説明 |
| SUM(数値1,数値2,…) | 数値を合計します。 |
LEN関数
| 書式 | 説明 |
| LEN(文字列) | 文字列の文字数を返します。 |
SUBSTITUTE関数
| 書式 | 説明 |
| SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象]) | 文字列中の指定された文字列を他の文字列に置き換えます。 |
MID関数
| 書式 | 説明 |
| MID(文字列, 開始位置, 文字数) | 文字列から抽出する文字の開始位置を指定し、指定された文字数の文字を返します。 |
MIN関数
| 書式 | 説明 |
| MIN(数値 1, [数値 2], …) | 最小の数値を返します。 |
FIND関数
| 書式 | 説明 |
| FIND(検索文字列, 対象, [開始位置]) | 文字列から指定された文字列を検索し、その文字列が最初に現れる位置を左から数え、その番号を返します。 |
RIGHT関数
| 書式 | 説明 |
| RIGHT(文字列,[文字数]) | 文字列の末尾 から指定された数の文字を返します。 |
エクセル関数の基本を外れた裏ワザ
ここでは、LEFT関数、MID関数、RIGHT関数を中心に、セル内の文字列から数字だけを抽出しました。
エクセル関数には、基本的な使い方から、全く基本を外れた裏技といっていい使い方があります。ここで紹介した方法がそういっていい使い方です。誰が最初に見つけたのかわかりませんが、一部のエクセル ユーザの間で有名な関数の使い方は、他にもあります。理屈なしに覚えておくと便利です。
こちらの記事もご覧ください。⇒エクセル関数で特定の文字列を抽出する方法！色々な文字列を抽出できます。
