Same identical values not treated as equal

[gg]

Good morning Excel experts!

I have two sets of data that I am comparing and they do not show as being equal despite they read the exact same.
Example:
B1: 19500
K1: 19500

When i test these values as being equal I get (=B1=K1) result [FALSE]

K1 data is created from a website down load and paste into the sheets.
both cells are formatted as GENERAL

In the past I could use the TEXT TO COLUMNS and make this work. However, the website changed and now the data is reading a different value.

Any ideas of how to makes these the same?

 

[gg]

Let me add.
I can put the cursor in the downloaded cell and press enter and the value clears and is equal.

 

[Rick Rothstein]

Good morning Excel experts!

I have two sets of data that I am comparing and they do not show as being equal despite they read the exact same.
Example:
B1: 19500
K1: 19500

When i test these values as being equal I get (=B1=K1) result [FALSE]

K1 data is created from a website down load and paste into the sheets.
both cells are formatted as GENERAL

In the past I could use the TEXT TO COLUMNS and make this work. However, the website changed and now the data is reading a different value.

Any ideas of how to makes these the same?

Check the values in Column K that show as not equalling the corresponding cell in Column B for a trailing character which may be either a normal space (ASCII 32) or (what I expect you will find since web pages are notorious for using them) a non-breaking space (ASCII 160). If you find either, or both, you can adjust your formula to one of these depending on what you find...

Space: =B1=TRIM(K1)

Non-Breaking Space: =B1=SUBSTITUTE(K1,CHAR(160),"")

Both: =B1=TRIM(SUBSTITUTE(K1,CHAR(160),""))


EDIT NOTE: I just read your second message which seems to indicate you may have a trailing normal space.

 

[Joe4]

That usually means that either:
1. One value is on Numeric data type, and the other is Text data type
or
2. They are both Text data types, but one has extra characters (usually spaces) in it

To check for the first situation, use the ISNUMBER function, i.e.

=ISNUMBER(B1)
=ISNUMBER(K1)

Note that if one is FALSE, simply changing the Format on the column does NOT change the Data Type! (formatting only affects numeric values entered as numbers).
You would have to use something like Text to Columns to convert those from Text to Numbers.

For the second situation, that can easily be checked like this:

=LEN(B1)
=LEN(K1)

to find which one has the extra spaces.

 

[gg]

Check the values in Column K that show as not equalling the corresponding cell in Column B for a trailing character which may be either a normal space (ASCII 32) or (what I expect you will find since web pages are notorious for using them) a non-breaking space (ASCII 160). If you find either, or both, you can adjust your formula to one of these depending on what you find...

Space: =B1=TRIM(K1)

Non-Breaking Space: =B1=SUBSTITUTE(K1,CHAR(160),"")

Both: =B1=TRIM(SUBSTITUTE(K1,CHAR(160),""))


EDIT NOTE: I just read your second message which seems to indicate you may have a trailing normal space.

What is the best way to remove the problem and make the equal each other?

I need to be able to use the B1 value and lookup other data by searching an array for an equal value to pull or summarize data. However, because the B1 value can't be found, I am getting a #N/A result. But, I know that I visually see that value in my array. It's just not being read as the same value? Is there a fail safe way so that the value is what I see?