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

2 options

1-
Convert all values to Number values.

2-
Another way to compare is to subtract them

Instead of

A1<<b1
B1

use

A1-B1<0

When you use the text values in an arithmetic operation they are automatically converted to text.</b1

 

[emirpk]

Thanks for helping me out PGC, you helped me out yesterday as well. I just want to be sure did you really mean that text values are converted to text when used in an arithmetic operation or did you mean to say that they are converted to numbers?

Thanks again.

 

[pgc01]

Hi

Arithmetic operations work with numbers. Excel recognises that and if you use a text value excel tries to convert it to number, in order to be able to perform the operation.

Ex.

Let's say you have 2 text values in

A1: 9
B1: 10

As text values "9" is bigger than "10"

If you use

A1<B1
<b1

then the result is FALSE

If you use

A1-B1<0

because of the subtraction excel will convert these values to number values and the result will be -1, which makes the result TRUE</b1

 

[emirpk]

Thanks for the great explanation PGC. I can see how numbers stored as text are compared differently using the logical operators, but is it safe to compare numbers stored as general format with numbers stored as number format?

 

[Daxton A.]

You can also use the int() function to convert the text to an integer.

For example: Int(Range("A1").Text) - Int(Range("B1").Text)) would equal -1

 

[pgc01]

...is it safe to compare numbers stored as general format with numbers stored as number format?

Hi

Excel doesn't care about the format when comparing values. Excel will only look at the values.

If you have a text value, it's not because you change the cell format that the value changes.

Do a simple test:

1 - Set format of B1 as General and write in B1: =ISTEXT(A1)
This tests if the value in A1 is a text value

2 - set format of A1 as text and write in A1: 123
Since the cell was previously formatted as text, the value "123" is a text value. Confirm that in B1 that should have TRUE

3 - select A1, and in Format Cells change its format to General.
You'll see that B1 still has TRUE. The fact that you changed the format of the cell B1 did not change the nature of its value

3 - select A1, and in Format Cells change its format to Number with 0 decimals
Again, you'll see that B1 still has TRUE. The fact that you changed the format of the cell B1 did not change the nature of its value

Conclusion: never rely on the format of the cell to conclude about the type of its value.

If you want to know if a value is a text value, use ISTEXT()
If you want to know if a value is a number value, use ISNUMBER()
etc.

Conclusion 2:

If you want to compare 2 values as number values, either you make sure that all the values are number values or use VALUE(), like

VALUE(A1)<VALUE(A2)

the function VALUE() will ensure that the values are compared as numbers values.

 

[emirpk]

Hi again,

Sorry to bring this topic back up but I'm still having issues with comparing my data. I have converted all my data to numbers but numbers that are similar still return false when compared using equals. Using the val function is a fix but is there a reason why this is happening? I converted all my data to numbers by going through the little green triangle in the corner of a cell, is this not the correct way to do it? Also testing for isnumber() and istext() on both values returns the same result, both are numbers but are not text. Thanks.

 

[pgc01]

If A1 and B1 have number values and

=(A1=B1)

returns False, then they do not hold the same value.

Try:

=A1-B1

to check the difference between them. It's probably a very small value and you are not seeing it because you're not seeing all the decimals. Please try.

 

[emirpk]

Thanks pgc, you are correct again. Not sure how this happened my data should be equal. Any reason why the test A1=B1 returns true if val() is used (i.e. val(A1) = val(B1))? I've tried setting the value of my cells to val(cell.value) and then the test A1=B1 returns true. Is it a good idea to do this with all my cells?

Thanks again.