「文字が数字になってしまう」事件

先日 Excel について、ある調査依頼を受けました。
記号だったはずの文字が、転記すると数字になってしまうというのです。

確認したところ、以下の記号が数字になっていました。
・0E0055　⇒　0.00E+00

これは偶然にも、同記号が「指数」の表記と一致していたことが原因のようです。さらに本ケースでは、一旦配列に格納した文字列を別のシートに転記していたため、元セルの書式情報は転記先に反映されていませんでした。

見たところ、配列に格納された時点では「0E0055」のままでしたので、次のように処理しました。

1. 正規表現で、指数として認識される文字になっていないか確認する。
2. なっている場合、先頭に「'」を追加する。

具体的には、以下のとおりです。

Function ExponentToString(target_string As Variant) As String
    ' Microsoft VBScript Regular Expression 5.5 参照済み。
    Dim myReg As RegExp
    Set myReg = New RegExp
        ' パターン定義。一文字以上の数字 + E + 一文字以上の数字
        myReg.Pattern = "^\d+E\d+$"
        If myReg.test(target_string) = True Then
            target_string = "'" & target_string
        End If
        ExponentToString = target_string
End Function

こちらでテストしてみました。

Sub test()
    ' 配列をそのまま貼り付け。
    Dim seq As Variant
        seq = Selection
        Selection.Offset(1) = seq
    
    ' 配列内の指数を文字に変換して貼り付け。
    Dim i As Long, j As Long
        For i = 1 To UBound(seq)
            For j = 1 To UBound(seq, 2)
                seq(i, j) = ExponentToString(seq(i, j))
            Next
        Next
        Selection.Offset(2) = seq
End Sub

結果は、以下の通りです。

一応、こちら↓でも可能なのですが、何かの拍子に数値に変換されるかもしれないので、今回は不採用としました。

Sub test2()
    Dim seq As Variant
        seq = Selection
        With Selection.Offset(1)
            .NumberFormatLocal = "@"
            .Value = seq
        End With
End Sub

理想を言えば、指数として認識されてしまう文字列は避けてほしいところですが、既にあるものは仕方ないですね。

参考まで。