Introduction
Ver. all 後半の説明から 2021 365
文字列の中から数字だけを抜き出す方法を紹介します。抜き出す文字の位置が決まっている場合は簡単で、そうでない場合は難しいので別々に紹介します。
２つに分けて紹介するにゃ
抜き出す数字の位置が決まっている場合の方法
抜き出す数字の位置が決まっている場合は簡単です。
例えば下のように常に３文字目から２文字分、次は６文字目から３文字分というように規則的な場合です。
数字とそれ以外の場所を分割して表示すれば良い場合は「区切り位置」機能を使用するのがお勧めです。数字部分だけを抜き出したい場合は関数を使用しましょう。
別々に説明します。
区切り位置機能を使用して数字を抜き出す方法
下の画像のように、文字の位置を指定して分割する方法です。
右側のセルに分割されたデータが上書きされるので、右側に他のデータが存在する場合は新しい列を挿入しておきましょう。
まず、対象のデータを選択して、
［データ］タブ → ［区切り位置］をクリック。
［区切り位置指定ウィザード］が開きます。
最初の画面では、［スペースによって右または左に揃えられた固定長フィールドのデータ］の方にチェックを入れて［次へ］をクリック。
２番目の画面で細かい設定をします。
設定するのは左下部分。
区切りたい場所をクリックすると、
文字を区切る位置を指定する縦線が表示されます。
この縦線は引かれた後でも、引っ張れば左右に移動することができます。
また、削除したい場合は上下に引っ張れば消えます。
今回の場合は、文字と数字を区切るために３か所に設定します。
設定できたら右下の［次へ］をクリック。
３番目の最後の画面では特に何もせず［次へ］をクリック。
これで、指定した場所で文字が区切られます。
数字部分だけを抜き出して他で利用したい場合は一番簡単な方法です。
関数を使用して数字を抜き出す方法
抜き出した数字を他の数式で使用したりする場合は、関数を利用しましょう。
文字を抜き出す関数を使用することで様々な状況に対応できます。
使用するのは
- LEFT関数
- RIGHT関数
- MID関数
の３つです。
これらを状況に応じて使い分けます。
左側から抜き出す
左側から何文字かを抜き出したい場合は、LEFT関数を使用します。
LEFT関数・・・文字列の先頭（左）から指定された数の文字を返す
=LEFT（文字列,文字数［省略可］）
- 文字列・・・取り出す文字を含むセルを指定
- 文字数・・・取り出す文字数を指定
下のように対象のセルと抜き出したい文字数を指定するだけで抜き出すことができます。
後は下にコピーすればOK。
右側から抜き出す
右側から何文字かを抜き出したい場合は、RIGHT関数を使用します。
RIGHT関数・・・文字列の最後（右）から指定された数の文字を返す
=RIGHT（文字列,文字数［省略可］）
- 文字列・・・取り出す文字を含むセルを指定
- 文字数・・・取り出す文字数を指定
下のように対象のセルと抜き出したい文字数を指定するだけで抜き出すことができます。
後は下にコピーすればOK。
途中を抜き出す
途中の何文字かを抜き出したい場合は、MID関数を使用します。
MID関数・・・文字列の指定した位置から、指定した文字数を取り出す関数
=MID（文字列,開始位置,文字数）
- 文字列・・・文字を取り出すセルを指定
- 開始位置・・・左から何文字目を取り出すか指定
- 文字数・・・取り出す文字数を指定
下のように対象のセルと抜き出したい位置、文字数を指定するだけで抜き出すことができます。
後は下にコピーすればOK。
それぞれの関数の引数指定は簡単なので、状況に応じて関数を使い分けましょう。
抜き出す数字の位置が不規則な場合の方法
※Excel 2019以降で使用可能なCONCAT関数や、365、2021以降で使えるSEQUENCE関数を使用しています。
抜き出し位置が、データによって異なる場合は、工夫が必要になります。
方法は幾つか考えることができますが、ここでは様々な状況でも対応できる方法を紹介します。
しかし、途中でバージョン2021以降か365でしか使用できない関数を使用するので旧バージョンを使用の方は参考程度にご覧ください。
下の「C2」に入れる数式ですが、
完成する式は次のようになります。
=CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))
長いですね・・・。
複数の関数を使用しているので、順番に式を取り出しながら説明していきます。
まず、 LEN(A2) からいきましょう。
=CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))
文字数を数えるLEN関数を使用します。
LEN関数・・・文字列の文字数を返す
=LEN（文字列）
- 文字列・・・文字数を調べるセルを指定
「C2」に
=LEN(A2)
の式を入れた結果が
下の画像です。
「A2」の文字数は６文字なので、「6」と表示されました。
次は、そのLEN関数を包んでいる、SEQUENCE関数です。
=CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))
このSEQUENCE関数は最近登場した関数で、数値を配列として返してくれます。
SEQUENCE関数・・・連続した数値を配列として返す
=SEQUENCE（行,列,開始,目盛り）
- 行・・・返される行数を指定
- 列・・・返される列数を指定［省略可］
- 開始・・・数列の最初の数値を指定［省略可］
- 目盛り・・・数列の後続の各値の増分量を指定［省略可］
「配列」とは、簡単に言うと一つの箱の中に、他の箱を用意することです。
下のようなイメージです。
今回は、最初の引数として「A2」の文字数を指定することで、文字数分の連続した箱を作成するイメージです。
実際に右隣のセルに
=SEQUENCE(LEN(A2))
と入力してみると、
６文字分の連続した数値がスピルされて表示されました。
※最近のバージョンでは、格納されている配列が下に滲み出るように表示されます。
「スピル」に関しての説明に興味のある方は、下のリンク先をご覧ください。
さて、次です。
MID関数を使用して、対象となるセルの文字を実際に取り出します。
=CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))
MID関数・・・文字列の指定した位置から、指定した文字数を取り出す関数
=MID（文字列,開始位置,文字数）
- 文字列・・・文字を取り出すセルを指定
- 開始位置・・・左から何文字目を取り出すか指定
- 文字数・・・取り出す文字数を指定
では、右のセルに
=MID(A2,SEQUENCE(LEN(A2))
と入れてみます。
「A2セルの全ての文字を1文字づつ取り出して」という意味になります。
配列を使用しているため、感覚的に分かり難いかもしれませんが、結果は下画像のように、「A2」の文字が取り出されます。
さて、ここから数字だけにする必要がありますね。
数字以外の場所を無理やりエラーが返されるように設定しましょう。
これは、数字の「1」を掛けてあげることで何とかなります。
=CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))
数字の「1」を掛けると、数字の場所はそのままの状態になりますが、対象が文字だった場合はエラーが返されるようになります。
=文字×1
って確かにおかしいですよね。
では、実際に入力。
=MID(A2,SEQUENCE(LEN(A2)),1)*1
これまでの式に *1 を追加しただけです。
結果は下のように、数字以外の場所には「#VALUE!」エラーが表示されます。
では、そのエラーが表示された場所は無視されるように設定しましょう。
使用するのはIFERROR関数です。
=CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))
IFERROR関数・・・指定した場所がエラーの場合、設定した値を返す
=IFERROR（値,エラーの場合の値）
- 値・・・エラーでない場合の数式を指定
- エラーの場合の値・・・エラーの場合に返す値を指定
隣りに
=IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””)
と入力してみます。
「もし、エラーでなかったら、数字を返して。エラーだったら空欄にして」という感じ。
結果、エラーの場所が空欄になります。
これで数字だけを抜き出すことができました。
では、最後に抜き出された数字を結合しましょう。
使用するのはCONCAT関数。
CONCAT関数・・・文字列を結合する
=CONCAT（<text1>,<text2>）
- <text〇>・・・結合したい文字列やセル範囲を指定
=CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))
これまで作成した式を囲むだけです。
これで完成。
「A2」の数字だけを取り出すことができました。
下にコピーして終了です。
まとめ
複雑な数式の場合は、部分ごとに動きを見ていくと理解しやすくなると思います。
「配列」は慣れるまで動きを理解するのが難しいかもしれませんね。
今回は、最近使えるようになったSEQUENCE関数とCANCAT関数が登場します。
SEQUENCE関数はアイデア次第で、便利に活用できそうな関数ですね。
数字以外をエラーにしちゃうのがポイントだにゃ