A列の内容を分割してB、C、D列にセットする例

IT 勉強 Excel VBA

肌荒れがヒドイbonlifeです。スキンケアしなきゃ！今日は人力検索はてなのEXCELに関する質問(http://q.hatena.ne.jp/1161146528)で「これは簡単だな。」と思って試しにVBAを書いてみたところ、思わぬ罠にハマって苦戦してしまいましたよ。忘れないうちにメモしておきます。A列の値から"-"(ハイフン)を削除した後、分割してB列、C列、D列にセットするだけのシンプルなVBAです。

Option Explicit
Sub Macro1()
    ' 前ゼロ消失防止のために文字列として扱う
    Columns("A:D").NumberFormatLocal = "@"
    ' A列の"-"(ハイフン)を削除
    Columns("A").Select
    Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells(1, 1).Select
    ' 最後の行の値を取得し、変数に格納
    Dim lastRow As Long
    lastRow = Columns("A").SpecialCells(xlLastCell).Row
    ' カウンタ用変数定義、初期化
    Dim i As Long
    i = 1
    ' 1行目から最後の行まで1行ずつ処理
    While i <= lastRow
        Cells(i, 2).Value = Left(Cells(i, 1).Value, 3)   ' 左3文字を2列目(B列)にセット
        Cells(i, 3).Value = Mid(Cells(i, 1).Value, 4, 4) ' 4文字目から4文字を3列目(C列)にセット
        Cells(i, 4).Value = Mid(Cells(i, 1).Value, 8, 4) ' 8文字目から4文字を4列目(D列)にセット
        i = i + 1
    Wend
End Sub

試してみたところ、上手くいくケースと、「あらら…」な結果になってしまうケースがありまして。なんだか置換処理を行うと先頭の前ゼロがなくなってしまう模様。例えば「042-1111-1111」という値から"-"(ハイフン)を削除するReplaceを行っただけなのに「4211111111」となってしまうのです。むむぅ。と思いながらアレコレ調べたところ、以下の仕様という名の問題に該当してしまっていることが発覚。

数値が含まれている文字列を、[置換] により、数値のみのデータに変換すると、表示形式は文字列のままですが、数値データとして 認識される場合があります。

ガビョーンですよ。これを仕様だと言い切れるMicrosoft最強説。とは言うものの、元データを破壊する置換を使った処理はそもそも適切ではないので、別のやり方にしてみました。

Option Explicit
Sub Macro1()
    ' 前ゼロ消失防止のために文字列として扱う
    Columns("A:D").NumberFormatLocal = "@"
    ' 最後の行の値を取得し、変数に格納
    Dim lastRow As Long
    lastRow = Columns("A").SpecialCells(xlLastCell).Row
    ' データクリア
    Range(Cells(1, 2), Cells(lastRow, 4)).ClearContents
    ' 変数定義
    Dim i As Long, cellValue As String
    i = 1 ' カウンタ用変数の初期化
    ' 1行目から最後の行まで1行ずつ処理
    While i <= lastRow
        cellValue = Replace(Cells(i, 1).Value, "-", "") ' 1列目(A列)の値からハイフンを削除し、変数に代入
        Cells(i, 2).Value = Left(cellValue, 3)          ' 左3文字を2列目(B列)にセット
        Cells(i, 3).Value = Mid(cellValue, 4, 4)        ' 4文字目から4文字を3列目(C列)にセット
        Cells(i, 4).Value = Mid(cellValue, 8, 4)        ' 8文字目から4文字を4列目(D列)にセット
        i = i + 1
    Wend
End Sub

こっちのReplaceは問題ナシです。分かりやすいし、確実ですね。
最後にこの件を調べていて見つけた参考になるページをご紹介。普段は気にしなくてもなんとかなりますが、知っておくと役に立つかもです。