用科学数字将单元格内容修剪到一定的长度

Question

我有一列数字，我需要将其保留为数字，但将长度限制为 20 个字符。

Sub casecheck()
Dim myString As String
Dim newString As String
Dim char As Variant
'replace all special characters with nothing
Const SpecialCharacters As String = "!,.,#,$,%,^,&,-,(,),{,[,],},/,, "
For i = 2 To trans
myString = Worksheets("Data Input").Cells(i, 10).Value
newString = myString
For Each char In Split(SpecialCharacters, ",")
newString = Replace(newString, char, "")
Worksheets("ExpertPay Feed").Cells(i, 3).Value = newString
Worksheets("ExpertPay Feed").Cells(i, 3).NumberFormat = "0"
Next
Next i
'trim length down to 20 characters
For i = 2 To trans
Worksheets("ExpertPay Feed").Cells(i, 3).Value = Left(Worksheets("ExpertPay Feed").Cells(i, 3).Value, 20)
Next i

End Sub

我的代码运行得非常好，除非我的数字很长。 例如：

这个原始数字：1.31137E+17变成了13113690920150400000000000000000 而且我似乎无法修剪掉所有多余的零！

Answer 1

试试这个函数：

Function TrimTrailingZeroes(BigNumber As String) As String
Dim i As Integer
For i = Len(BigNumber) To 1 Step -1
If Mid(BigNumber, i, 1) <> "0" Then
TrimTrailingZeroes = Left(BigNumber, i)
Exit Function
End If
Next i
TrimTrailingZeroes = BigNumber
End Function

有时，将数字视为文本是一种简单的方法。