Excelで文字列の中から数字のみ取り出す（ユーザー関数）

久しぶりの投稿ですが今日はExcelの話題。前職で管理業務を任されていた時はExcelとAccessは欠かせないツールでしたが、今はCSVファイルの整形程度でほとんど凝った使い方はしません。しかし商品管理では何かしら出てくる要件があるのです。今日は在庫管理を担当しているスタッフから相談がありました。

ご存じかどうかは分かりませんが、スポーツ用品メーカーが作るウェアなどのアパレル製品には、「サイズ」の他に「色」というラインナップがあります。大抵のメーカーは独自に色名に番号を付けて管理しています。

テニス用品ではYONEXが品番管理がやりやすいので（FILA-レナウンはなんと年度で色番号が変更になる！！）例として出してみます。YONEXの代表的な色とコードは下記のようになります。

• ブラック（007）
• ホワイト（011）
• クリアーレッド（459）
• ネイビーブルー（019）
• チャコール（075）

とまぁこんな感じです。

クリアーレッドはいいですねー（459）ですからそのまま数値です。しかし、それ以外はなんと頭に0が付いてます。これがExcelでの処理で泣かせどころなのです。Excelは中途半端に賢いので数字を数値として認識していまいます。

スタッフからうけた相談は、この色名（コード番号）の中からコード番号のみ取り出す関数はどうやったらよいか？とのことでした。私の知る限り会社で使っているExcel2003にはそんな便利な関数は無いハズです。

ユーザー関数作るしか無いじゃん・・たぶん

という訳でユーザー関数を使う前提でGoogleせんせーにご質問したところ、あっさりそれをやっている例が見つかりました。

Excel(エクセル) VBA入門：セル内の文字列から数字を取り出す

ありがたいことに、サンプルコードが3つも載っています。プログラマーはすごいなぁ～（しつこい様ですが私はプログラマーじゃないです）

手っ取り早く一番目のサンプルコードを使わせてもらう事にしました。

Function myNo1(r As Range)
　Dim myStr As String
　Dim myN As String
　Dim i As Long
　　For i = 1 To Len(r.Value)
　　　　myStr = Mid(r.Value, i, 1)
　　　　If myStr Like "[0-9]" Then
　　　　　　myN = myN & myStr
　　　　End If
　　Next i
　　If IsNumeric(myN) Then
　　　　myNo1 = myN * 1
　　Else
　　　　myNo1 = ""
　　End If
End Function
Code language: JavaScript (javascript)

ユーザー関数の組み込み方は詳しくは記しませんが、ツール->マクロ->Visual Basic Editorで出来ます。このサンプルコードの場合は、関数名が「myNo1」になりますね。

んでもってこのサンプルスクリプトをそのまま組み込んで関数を使って色情報から色コードを取得したところ数値の取得は出来ました。ところが「007」として欲しいのに「7」と返ってきます・・・・orz

=myNo1(B1)
7

そんじゃ文字列に変換する関数を通せば良いじゃん

=T(myNo1(B1))
7

あかーん、やっぱり「7」で返ってくる。

ふと思ったのは、myNo1が返してくれる値自体が数値になってるんじゃないか？つまりこの時点で先頭の0が落ちてる可能性大では・・・強制的に文字列化してしまえっと。

=CONCATENATE("'",myNo1(B1))
'7

これで決定的。となるとmyNo1をカスタマイズしないと解決しない。よくよくチェックしてみたら「myNo1 = myN * 1」という部分があるのに気づいた。1をかけ算するという事は無意味だけど意味がある。つまりここで数値化していると推測して下記のようにかけ算をしない様にカスタマイズした。

Function myNo1(r As Range)
　Dim myStr As String
　Dim myN As String
　Dim i As Long
　　For i = 1 To Len(r.Value)
　　　　myStr = Mid(r.Value, i, 1)
　　　　If myStr Like "[0-9]" Then
　　　　　　myN = myN & myStr
　　　　End If
　　Next i
　　If IsNumeric(myN) Then
　　　　myNo1 = myN
　　Else
　　　　myNo1 = ""
　　End If
End Function
Code language: JavaScript (javascript)

これで無事に文字列としてカラーコードを返してくれる関数にカスタマイズ出来た。

=myNo1(B1)
007

ともかくこれで目的は達成である。私は自分では作れないけどちょっとしたカスタマイズなら勘を働かせたらなんとかなるというのは学習しているのでこれくらいの短いスクリプトならなんとかなるだろうと思って諦めずに試行錯誤している。

もちろん100行とかになるとお手上げだ・・・

作ったソースを公開して下さっている事に感謝。ありがとうございます～