CODEWITHSUNDEEP
excelvba
NineTailedFoxOne
NineTailedFoxOne

Reputation: 3

How to truncate number to first eight digits?

I'm working on a macro that truncates extra digits from the end of an 8 digit account number.

I've tried using different types of loops. 

'Asks how many entries then inputs it into FOR loop
entryNum = InputBox("Number of Entries: ", "Account Number Truncator")

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

Numbers like 547890012508973240987 get converted into 5478.900 when it is supposed to be 54789001 and it only seems to work on numbers with lesser digits.

Upvotes: 0

Views: 213

Answers (1)

Tim Williams
Tim Williams

Reputation: 166685

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://learn.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

Upvotes: 3

Related Questions