Excelの文字列で数字のみを抽出する

Excelで名前+学籍番号などの「Myouji Namae 123456」などのように、英数字が混ざっている場合に数字の123456だけ抽出したい場合の関数の使用例をメモ

A1に名前+学籍番号の情報があり、B1に数字のみを抽出するexcel1

Myouji Namae 123456 =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))

B1に上記関数を入力すると、結果は以下のようになる
(数字のみを抽出する方法は他にもいろいろ関数の組み合わせが存在する)

excel2

Myouji Namae 123456 123456

関数の意味をひとつずつ解析すると・・・(Excel2007の数式の検証で解析)

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))

①=MID(“Myouji Namae 123456”,MIN(FIND({0,1,2,3,4,5,6,7,8,9},
”Myouji Namae 1234560123456789")),LEN(A1)

②=MID(“Myouji Namae 123456”,MIN({20,14,15,16,17,18,19,27,28,29}),LEN(F15))

MIN({20,14,1,16,17,18,19,27,28,29}・・・という計算過程は以下のようになる。

excel3

Myouji Namae 1234560123456789という文字列の中から
0を検索・・・20番目(スペースも含む)
1を検索・・・14番目(スペースも含む)


これで一番小さい14番目から数字で始まっていることが分かり、③以降の計算式で14が使われる。

③=MID(“Myouji Namae 123456”,14,19)

最終的には以下の関数を実行していることになる

④=MID(A1,14,19)

関数のポイントは

(1)FIND({0,1,2,3,4,5,6,7,8,9})の配列の部分
(2)&”0123456789”の部分

(1)は0-9の全ての数字を検索する必要があるため。{0-9}という書き方でも良い
(2)はFIND関数では文字列の中に対象の文字がない場合エラーが表示され計算が中断される。文字列の後方に検索する文字をくっつけることでエラーを回避している

具体的にエラーが表示される例として

excel4

abc123456789 =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1))

B1に上記関数を入力すると、結果はエラーとなる。

excel5

abc123456789 #VALUE!

これは0の検索に失敗したために「#VALUE!」というエラーとなる。

検索対象に全ての数字があるとは限らないので、&”0123456789”でダミーを全てくっつけてしまうと
いうエラー回避をするということになる。

参考になったURL

http://oshiete.home4u.jp/qa4675382.html
http://oshiete1.goo.ne.jp/qa2527530.html

広告
カテゴリー: Office パーマリンク

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中