Comparing numbers stored as text

[emirpk]

Hi,

I have a bunch of numeric values I want to compare but some are marked as numbers stored as text and some are not. This causes some problems when I use the logical operators for comparison, for example 0.10 < 0.08 would return true. What is the best way to get around this? Should I convert the numbers stored as text to numbers? Or convert all the values to text (not sure if the logical operators work as expected when applied to numbers stored as text)? I'm looking for the safest way because I do not want to mess with my original data too much.

Thanks.

 

[pgc01]

Hi

I don't know your data and so I don't know the problem

One thing that could happen is that A1 and B1 are not numbers, but text values.

In that case, when they are converted to number values they may lose the last digits.

For ex.

Sub Test()
Dim a1 As String, b1 As String
Dim v1, v2

a1 = "1234567890123456789"
b1 = "1234567890123456788"

MsgBox a1 = b1
MsgBox Val(a1) = Val(b1)
End Sub

This is just a guess, of course, I don't know your data.

 

[Rick Rothstein]

Hi

I don't know your data and so I don't know the problem

One thing that could happen is that A1 and B1 are not numbers, but text values.

In that case, when they are converted to number values they may lose the last digits.

For ex.

Sub Test()
Dim a1 As String, b1 As String
Dim v1, v2

a1 = "1234567890123456789"
b1 = "1234567890123456788"

MsgBox a1 = b1
[COLOR=#FF0000][B]MsgBox Val(a1) = Val(b1)[/B][/COLOR]
End Sub

This is just a guess, of course, I don't know your data.

To preserve the value out to 28 digits, use CDec instead of Val...

MsgBox CDec(a1) = CDec(b1)

 

[pgc01]

Well, the truth is that if they are strigs why do you convert them to anything? If you want to compare their values why not compare them directly

MsgBox a1 = b1

Like before this is just guessing, of course, I don't know your data and I don't know what you need.

 

[Rick Rothstein]

Well, the truth is that if they are strigs why do you convert them to anything? If you want to compare their values why not compare them directly

Perhaps they could be of different lengths?