[Excel] 文字列先頭の数字部分を数値化したい（ValueとLeftとIfError）

• Excelで文字列先頭の数字部分を数値化するには、Value関数、Left関数、IfError関数を組み合わせます。
• 数字部分がどんな桁数でも対応できるようにするにはVBAを使用したカスタム関数が有効です。

=IFERROR(VALUE(LEFT(B4,4)), VALUE(LEFT(B4,3)))

1. 文字列先頭の数字を取り出したい

1-1. Value関数とLeft関数

ExcelではValue関数を使って文字列を数値にできます。
ただし、Value関数は文字列に数値以外の文字が含まれているとエラー（#VALUE!）になってしまいます¹。

そこで、まずは、Left関数を使って数値部分を切り出します。

=Value(Left(B4, 3))

B4セルに "104田中" が入力されていれば、
まず Left関数で "104" にしてから Value関数の結果 104 を出すわけです。

2. IfError関数で場合分けする

ところが、IDの桁数が揃わない場合にうまくいきません。

LEFTの文字数を3にすると 4桁の数字がおかしくなりますし、4にすると 3桁の数字がエラーになります。

そこで、IfError関数を使います。
たとえば、3桁と4桁だけなら、以下のような数式にします。

=IFERROR(VALUE(LEFT(B4,4)), VALUE(LEFT(B4,3)))

まず 4桁とみなしてValue関数で数値を求めます。
3桁の場合はそこでエラーになるので、今度は Left関数で3桁分にして数値化します。

2-1. 桁数の分だけIfError関数を入れ子にする

もう少し一般化してみます。
もし、最大の桁数が 5桁なら、もう少しIfError関数を重ねていきます。

=IFERROR(VALUE(LEFT(B4,5)), IFERROR(VALUE(LEFT(B4,4)), IFERROR(VALUE(LEFT(B4,3)), IFERROR(VALUE(LEFT(B4,2)), VALUE(LEFT(B4,1))))))

桁数が増えるほど式は複雑になりますが、会員番号などの場合はこれで十分なことが多いです。

3. カスタム関数で解決する方法（Excel VBA）

もっとスマートな解答はないの？

Excel VBAが利用できる環境なら、どの桁数にも対応できる方法があります。
VBAでカスタム関数を作れば、このような長い数式を使わずに済みます。

Function ExtractID(inputString As String) As Long
    Dim i As Integer
    Dim idPart As String
    
    ' 文字列の先頭から順に数字かどうかをチェック
    For i = 1 To Len(inputString)
        If Not IsNumeric(Mid(inputString, i, 1)) Then
            Exit For
        End If
        idPart = idPart & Mid(inputString, i, 1)
    Next i
    
    ' 抽出したID部分を数値に変換して返す
    If idPart <> "" Then
        ExtractID = CLng(idPart)
    Else
        ExtractID = 0 ' IDが見つからない場合は0を返す
    End If
End Function

このコードをExcelのVBAエディタに貼り付ければ、そのブックでカスタム関数を使えるようになります。

• Alt + F11 でVBAエディタを開きます。
• 挿入 > モジュール を選択し、新しいモジュールにコードを貼り付けます。

あとは、Excelシート上のセルに以下のように入力します。

=ExtractID(B4)

3-1. 文字列内の数字の位置が先頭に限定しない場合のカスタム関数

文字列から数字だけを抽出して数値化するカスタム関数を作ることもできます。

文字列内のすべての数字を抽出し、それらを連結して1つの数値として返します。

Function ExtractNumbers(inputString As String) As Double
    Dim i As Integer
    Dim numberPart As String
    
    ' 文字列内のすべての数字を抽出
    For i = 1 To Len(inputString)
        If IsNumeric(Mid(inputString, i, 1)) Then
            numberPart = numberPart & Mid(inputString, i, 1)
        End If
    Next i
    
    ' 抽出した数字を数値に変換して返す
    If numberPart <> "" Then
        ExtractNumbers = CDbl(numberPart)
    Else
        ExtractNumbers = 0 ' 数字が見つからない場合は0を返す
    End If
End Function

カスタム関数は文字列を入力して数値を返します。

=ExtractNumbers(A1)

たとえば、
セルに “テスト100円” と入力 → 関数の結果: 100
セルに “abc123def456” と入力 → 関数の結果: 123456

こちらもどうぞ。

[Excel] Match関数とOffset関数を組合せて表から「検索」する

表の中からIDに該当する値を検索するには、Vlookup関数を使うのが一般的です。しかし、Vlookup関数では、検索列が表の左端にある必要があります。Match関数とOffset関数を組み合わせると、Vlookupよりも柔軟な検索ができます。Match関数とOffset関数のメリット検索列が左端にない場合でも使用可能横方向の検索も簡単に行える表から逆参照したい場合たとえば、以下のような予約データから顧客IDを元に予約時刻を逆算してみます。予約IDなら検索列が左端にあるので、...

chiilabo.com

[Excel] 小数点以下を切り捨てる関数（INT, RoundDown）

小数点以下を切り捨てるだけなら INT関数が手軽です。切り捨ての桁を変えたいときには RoundDown関数が役立ちます。切り捨てにはINT関数かRoundDown関数小数点以下を切り捨てるには、INT関数かRoundDown関数を使います。INT関数は、「整数（Integer）」にする関数です。与えた値を最も近い整数に切り捨てます。RoundDown関数は、「切り捨て（RoundDown）」るです。小数第何位で切り捨てるのか、桁数を選択できます。桁数にマイナスも指定すると、...

chiilabo.com

1. #VALUE は、”入力した数式に問題があるか、参照先のセルに問題があります” と Excel が伝えていることを意味します。 – エラー値 #VALUE! を修正する – Microsoft サポート

QRコードを読み込むと、関連記事を確認できます。