Left command in VBA

[tpthatsme]

Hello Mr. Excel community,

I have a small project that seems to be beating me and I need your help. I have a column of data that could be letters and numbers or all numbers (PartNo). I would like to change the value to the first 5 characters (Base5). I can do this as a formula in Excel, but my VBA isn't yielding the same results with long numbers. If the data is a long number (ex. 20 digits long), my VBA returns a Left 5 of an exponent number (ex. VBA: 65181651626513100000 returns 6.518 and formula returns 65181). I really need to keep this in VBA because I will be running this macro on new files daily.

I am not sure if my VBA is the most efficient, so any help would be great with this as well.

Sub Base5()

Dim Base5 As Range
Dim PartNo As Range
Dim i As Long
Dim AllRecords As Long




Set Base5 = Sheet1.Range("U2")
Set PartNo = Sheet1.Range("L2")
AllRecords = Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To AllRecords
    Cells(i, "U").Value = Left(Cells(i, "L").Value, 5)
Next i
'Column title: This tells us when the macro is done.
Range("U1").Value = "Base5"
End Sub

Thanks for your help!

 

[Fluff]

try

Cells(i, "U").Value = Left(Format(Cells(i, "L").Value, "0.0"), 5)

 

[tpthatsme]

Thanks, Fluff! That fixed it all up! Thanks for the help.

 

[Fluff]

You're welcome & thanks for the feedback