Macro keeps turning large numbers I am trying to t

Question

I'm working on a small macro for work that truncates extra erroneous numbers from the end of an 8 digit account number.

I've tried using different types of loops, looking online for the issue though its sort of hard to look for and have no idea how something with the code I have.

entryNum = InputBox("Number of Entries: ", "Account Number Truncator") 'Asks how many entries then inputs it into FOR loop
For counter = 2 To entryNum
    acctCor = Len(Cells(counter, 1))
    While acctCor > 8 'Loop to check the length of the number and then removes the last erroneous digits
        Cells(counter, 1).Value = Left(Cells(counter, 1), Len(Cells(counter, 1)) - 1) 'This is the part that's supposed to truncate the number
        acctCor = Len(Cells(counter, 1))
    Wend
Next counter

stuff like 547890012508973240987 gets converted into 5478.900 (7 digits not including the decimal) when it is supposed to be 54789001 and it only seems to properly work on numbers with lesser digits

Any help is greatly appreciated.

Answer 1

You can use Left() instead of chopping off digits one by one:

entryNum = InputBox("Number of Entries: ", "Account Number Truncator") 'Asks how many entries then inputs it into FOR loop
For counter = 2 To entryNum
    With Cells(counter, 1)
        .NumberFormat = "@"
        .Value = Left(.Value,8)
    End With
Next counter

Also - be very careful with numbers in Excel if you're not planning to use them as actual numbers (ie. they're just identifiers - you're not going to be doing any math on them)

For example 547890012508973240987 is beyond the limit of Excel's 15-digit precision.

https://support.office.com/en-us/article/keeping-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7

https://docs.microsoft.com/en-us/office/troubleshoot/excel/long-numbers-incorrectly-in-excel

https://superuser.com/questions/413226/why-does-excel-treat-long-numeric-strings-as-scientific-notation-even-after-chan