文字と数値を分割する

---

ABCなどは文字です。123などは数値です。性質の異なるデータですが、実務では「ABC123」のように、文字と数値を組み合わせて、何かの符号とすることが多いです。こういう場合はたいてい「ABC」に何かの意味があり「123」が何かを表しています。マクロで何かをするとき、文字だけ、あるいは数値だけを抜き出したいことがあります。ちなみに「ABC-123」のように記号で区切ってあったらSplit関数ですね。記号で区切られていなくて、さらに文字数や桁数も不定というケースです。

数値+文字 のケース

「123ABC」とか「100tanaka」みたいなケースです。これは簡単ですね。Val関数で一発です。文字列形式の"123"や"100"などを数値に変換するのがVal関数です。VBAは、自動的に型を変換してくれることが多いので、めったに使いませんが、Val関数には便利な特性があります。それは、変換対象の文字列を左から見ていって数値に変換できるところまでを変換してくれるという特性です。数値に変換するということは、結果的に文字部分は除去されるということです。

Sub Sample1()
    MsgBox Val("123ABC")
End Sub

じゃ、除去しちゃった「ABC」はどうやって特定するのか。これも簡単ですね。よろしいですか？「123ABC」のうち「123」を取り出せたんです。だったら、元の「123ABC」から、その「123」を消してやればいいんです。そう、Replace関数ですね。

Sub Sample2()
    Dim A As String, N As Long
    A = "123ABC"
    N = Val(A)
    MsgBox Replace(A, N, "")
End Sub

なので、こんな感じです。

Sub Sample3()
    Dim i As Long
    For i = 1 To 9
        Cells(i, 2) = Val(Cells(i, 1))
        Cells(i, 3) = Replace(Cells(i, 1), Cells(i, 2), "")
    Next i
End Sub

文字+数値 のケース

「ABC123」のように、文字が左側にあったらVal関数を使えませんね。Val関数は、変換対象を左からチェックしますから。左に文字があったら使えません。残念です。これがもし、数値+文字 のように逆だったらVal関数なんですけどね～逆だったらなぁ～逆にならないかなぁ～…。じゃ、逆順にしちゃいましょう。StrReverse関数は、指定した文字列を逆順にした文字列を返します。

Sub Sample4()
    MsgBox StrReverse("ABC123")
End Sub

いま心の中で「へぇ～そんな関数あるんだ。知らなかった～」って思ったでしょ。知らないのも無理はありません。ほとんど使い道はないですから。てゆーか、今回のようなケースくらいでしか使わないでしょう。間違っても、日本国民全員が日々使う関数ではありません。

「ABC123」を文字と数値に分割するには、まず「ABC123」を「321CBA」と逆順にします。その結果に対して、左から数値に変換できるところまでをVal関数で数値化します。

Sub Sample5()
    MsgBox Val(StrReverse("ABC123"))
End Sub

数値として抜き出したこいつを、また逆順にして、元に戻してやります。

Sub Sample6()
    MsgBox StrReverse(Val(StrReverse("ABC123")))
End Sub

素晴らしい！拍手が聞こえるようです。(￣∧￣)

でも、待ってください。これ、致命的な欠陥があります。「ABC123」だったら上手くいきます。でも「ABC120」だと望む結果になりません。

「ABC120」の場合、まず逆順にすると「021CBA」です。これをVal関数で数値化すると「21」になります。だってVal関数は"数値"に変換する関数ですから。「021」というのは文字ですから。さあ、困った。どうしましょう。

発想を柔軟にすれば解決できます。「021」のように1桁目に"0"があるからいけないんです。だったら、1桁目を"0ではない数値"にしてしまえばいいです。

Sub Sample7()
    MsgBox StrReverse(Val(StrReverse("ABC120" & "9")))
End Sub

別に"9"でなくてもいいです。好きな数値を最後につけてください。

1. "ABC120" & "9" → "ABC1209"
2. StrReverse("ABC1209") → "9021CBA"
3. Val("9021CBA") → 9021
4. StrReverse("9021") → 1209

最後に、一番後ろの"9"を除去します。いろんな方法が考えられますけど、ここは古典的な方法でいきましょうか。

Sub Sample8()
    Dim A As String
    A = StrReverse(Val(StrReverse("ABC120" & "9")))
    MsgBox Left(A, Len(A) - 1)
End Sub

なので、こんな感じです。

Sub Sample9()
    Dim i As Long, A As String
    For i = 1 To 9
        A = StrReverse(Val(StrReverse(Cells(i, 1) & "9")))
        Cells(i, 2) = Left(A, Len(A) - 1)
        Cells(i, 3) = Replace(Cells(i, 1), Cells(i, 2), "")
    Next i
End Sub

それ以外の複雑なケース

「123ABC456」とか「ABC123DE456F」のように複雑なケースになったら、これはもう簡単にはいきませんね。こうなったら、左から1文字ずつチェックして、数値→文字 あるいは 文字→数値 に変わる場所を判定し、そこで分割するしかありません。これ、けっこう面倒くさいので、Functionを作りました。ご自由にお使いください。なお「SNSplit」の"SN"は"String,Number"の略(のつもり)です。ネーミングのセンスが悪くてすみません。

Sub Sample10()
    Dim A As Variant, i As Long, j As Long
    For i = 1 To 18
        A = SNSplit(Cells(i, 1))
        For j = 0 To UBound(A)
            Cells(i, j + 2) = A(j)
        Next j
    Next i
End Sub

Function SNSplit(A As String)
    Dim i As Long, flag As Boolean, B As String, cnt As Long, C() As String
    flag = IsNumeric(Left(A, 1))
    For i = 1 To Len(A)
        If flag <> IsNumeric(Mid(A, i, 1)) Then
            ReDim Preserve C(cnt)
            C(cnt) = B
            B = ""
            cnt = cnt + 1
            flag = Not flag
        End If
        B = B & Mid(A, i, 1)
    Next i
    ReDim Preserve C(cnt)
    C(cnt) = B
    SNSplit = C
End Function