0

I am having issues with the Excel trim function. A lot of the times the data I extract is accompanied with extra white spaces after the string of information e.g., "1234566 ". I have historically used the trim function in VBA and it seems to do the job well, but I have stumbled an issue when it comes to cells with long strings of just numbers.

Here is the code:

Dim C As Range
For Each C In ActiveSheet.UsedRange
    With C
    C.Value = Trim (C)
    End With
Next C

This works for the majority of cases but e.g., if I use this code on along digit with spaces to the right "12355557899200123 ", Excel's trim function appears to Trim but change the value of the number (not good) by cutting off several of the last digits in this case 12355557899200123 became -> 12355557899200100 and 23 were replaced by 00 which can lead to miscalculations later :(((((.

Thank you in advance for your help and suggestions!

SJR
  • 22,986
  • 6
  • 18
  • 26
matc
  • 29
  • 4
  • 1
    Has nothing to do with `trim`. That has to do with with the topic floating point numbers. Maybe you start reading [here](https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result) and [here](https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html) on this topic. – Storax Apr 22 '20 at 10:44

2 Answers2

0

Excel has a precision of 15 digits, see here for these kind of limitations (Scroll down to Calculation specifications and limits). 12355557899200123 has a length of 17 digits which means you will loose the last two digits when you place this into an Excel sheet.

Storax
  • 11,158
  • 3
  • 16
  • 33
0

Your problem is not caused by Trim itself. Your original cell content is a String (else it couldn't have any spaces). Trim itself returns also a string, but when you assign the result to a cell, Excel will see that the string contains only digits and convert it automatically into a number. This number is represented as Double and has a simply a limited precision. You will get exactly the same result when you enter 12355557899200123 into a cell manually.

Only work around I can think of is to store the number as string (C.Value = "'" & Trim (C)). But you will still loose the least significant digits when you do calculations within Excel. You would have to do all calculations within VBA using the datatype Decimal and then write back the results as String to cells. To deal with Decimal, you have to declare a variable as Variant and use the function CDec to convert a value (eg a string) into a decimal.

Dim v As Variant
v = CDec(ActiveCell)
v = v * 2
ActiveCell.Offset(0, 1) = "'" & CStr(v)
FunThomas
  • 23,043
  • 3
  • 18
  • 34