文字列中の数値を取り出すマクロ

ユーザー関数 Excel 2013 マクロ講座 65回

文字列から数値だけを抽出したい

Web上のデータや資料として渡されたCSVデータをExcelで開いたら、文字列の中に数字が混在して困ってしまうことがあります。
Excelでセルから数値のみを抽出したい、文字のみを抽出したいという要望は、既存の関数では対処できない場合もあります。文字列の長さや数値に規則性がない場合です。
今回は、文字列の長さが一定でない場合にも対処できる「文字列から数値だけを抽出するユーザー定義関数」を作ります。
（サンプルファイルは、こちらからマクロ65回サンプルデータ）

1文字ずつチェックして数値だけを抽出

以下のような本来価格だけを表示したいセルに文字列も混在している表から、数値だけを隣のセルに取り出すケースを考えます。
文字列から数値を取り出すユーザー関数マクロ1

ユーザ定義関数をシートで入力すると以下のようになります。
プロシージャ名（関数名）は、Getcellfigとしています。（これは自分にとって分かりやすい名前をつけてかまいません。）
=Getcellfig(B3)
文字列から数値を取り出すユーザー関数マクロ2

このユーザー定義関数を使うには、Visual Basic Editorを起動して、標準モジュールを挿入して、以下のコードを記入します。

Function Getcellfig(範囲 As String) As Long
  Dim 数値 As Long 
  Dim i As Integer 
    For i = 1 To Len(範囲)
      If IsNumeric(Mid(範囲, i, 1)) Then
        数値 = 数値 & Mid(範囲, i, 1)
      End If
    Next i
  Getcellfig = 数値
End Function

使っているVBA関数は、Len関数,IsNumeric関数,Mid関数となります。文字列から数値を取り出すユーザー関数マクロ3

ユーザ定義関数を作る解説

ユーザ定義関数を作るには、VBEエディターを起動し、Functionプロシージャの構文にそってコードを入力します。
文字列から数値を取り出すユーザー関数マクロ4

LEN関数でセルの文字数を数える

簡単な例で解説していきましょう。B1セルには、5つの文字列が入力されています。その１つ１つを順番にチェックして数値なら、変数とした数値に加えて行くのです。
文字列から数値を取り出すユーザー関数マクロ5

LEN関数で指定したセルの文字数を数えます。
B1セルは5文字ですので、Len(”B1”)の戻り値は5になります。
For i = 1 To Len(範囲)は、For i = 1 To 5と同じになりますね。

IsNumeric関数で文字が数値かどうかを判定

IsNumeric 関数は、引数 expression が数値として認識できる場合は真 (True) を返します。
それ以外は偽 (False) を返します。

Mid関数で指定文字数を取り出す

MID関数は、文字列の任意の位置から指定された文字数の文字を取り出します。
文字列から数値を取り出すユーザー関数マクロ6

For Nextステートメントで、文字数分のIfステートメントを繰り返して、文字列中の数値を取り出すわけです。
解説付のコードです。

Function Getcellfig(範囲 As String) As Long
  Dim 数値 As Long 'セルに含まれる数値をLong型の変数に
  Dim i As Integer '文字列の1つ1つの順番
    For i = 1 To Len(範囲) '文字数分繰り返す
      If IsNumeric(Mid(範囲, i, 1)) Then '数値ならば
        数値 = 数値 & Mid(範囲, i, 1) '変数数値に数値を追加する
      End If
    Next i
  Getcellfig = 数値
End Function