# Thread: CStr truncating trailing zeros from decimals

1. ## CStr truncating trailing zeros from decimals

I'm attempting to compare values from two different tables. Some of these values are strings, some numbers, some numbers with eprcentages etc.

To make it easier, I have been converting them all into a string using CStr but I've just noticed that CStr truncates trailing zeros from the decimal portion of numbers e.g. CStr(12.150) becomes 12.15.

Is there a way to stop this happening? It's causing my test to give false negatives.

2. ## Re: CStr truncating trailing zeros from decimals

Hello dougal,

If you think you can know the number of digits after the decimal point, so you can use the following:

Cstr(Formatnumber(Expression [,NumDigitsAfterDecimal))

For your example you can write:

val=12.150

Cstr(Formatnumber(val ,3))' assuming all you numbers have 3 digits after the decimal point.

And you will get 12.150 as a string value.

Thanks.

3. ## Re: CStr truncating trailing zeros from decimals

Sadly not. It could be any number of digits. I don't really want to truncate both numbers because then I'll lose accuracy.

4. ## Re: CStr truncating trailing zeros from decimals

Try to convert both the numbers to double and compare.

5. ## Re: CStr truncating trailing zeros from decimals

That's what I ended up doing. Unfortunately, the values aren't salways numbers so it's added an extra level of complexity ot the code.

6. ## Re: CStr truncating trailing zeros from decimals

[ QUOTE ]

Unfortunately, the values aren't salways numbers so it's added an extra level of complexity ot the code.

[/ QUOTE ]
check with IsNumeric function.

7. ## Re: CStr truncating trailing zeros from decimals

That's what I did. It means I have to have an if statement to check. If I'd have been able to convert everything to a string, that wouldn't have been necessary.

8. ## Re: CStr truncating trailing zeros from decimals

Hello dougal1709 ,

if you are using excel for exporting the 2 table in 2 different sheets, u can simply use the below code. its great and it works awesome. i am scripting for similar situation. so i too was facing the same issue. then i apllied the below logic. and it works good

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook2= objExcel.Workbooks.Open(" ur file path" )
Set objWorkbook1= objExcel.Workbooks.Open("other file path" )
Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)
For Each cell In objWorksheet1.UsedRange
If IsNumeric(c1) and IsNumeric(c2) Then

print "Difference is = " &amp; c1

cell.Interior.ColorIndex = 16
Mismatch=0

Reporter.ReportEvent micPass, "Data match found","excel is highleted with grey cell"
else
cell.Interior.ColorIndex = 3
mismatch=1
Reporter.ReportEvent micWarning, "Data mismatch found","excel is highleted with red cell"
End If

cell.Interior.ColorIndex = 3
Mismatch=1
else
Mismatch=0
Reporter.ReportEvent micPass, "Data match found","excel is highleted with pink cell"
cell.Interior.ColorIndex = 15
End If
Next
objWorkbook2.save
objWorkbook1.Close
objWorkbook2.Close
objExcel.Quit
objExcel.Application.Quit

Alternatively u can modify ur script as per your requirment. Also, my suggestion would be, do not use Cstr. instead use isnumeric function.it gives you true or false valuethen you can aplly your conditions

Thanks honey.

9. ## Re: CStr truncating trailing zeros from decimals

Hello dougal1709 ,

was the issue resolved?

Thanks
Honey

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.