Why???

[Rob_010101]

To say this drives me nuts is an understatement.. I have 250,000 odd rows of data to check. I want to see if the post code in Column H matches the post code in Column O.

There's no erroneous spaces, they're formatted the same (as text)....

This is a CONSTANT issue with excel and I have never been able to work out why it does this when the data is OBVIOUSLY the same.

Can anyone help?

Thanks

 

[alansidman]

Cannot replicate your issue. Have you tried Trim on each column of data and then run your code?

Book4
	A	B	C
1	Post Code	Address
2	NN169PE	NN169PE	TRUE
3	NN169PE	NN169PE	TRUE
4	NN169PE	NN169PE	TRUE
5	NN169PE	NN169PE	TRUE
6	NN169PE	NN169PE	TRUE
7	NN169PE	NN169PE	TRUE
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=A2=B2

 

[Joe4]

This is a CONSTANT issue with excel and I have never been able to work out why it does this when the data is OBVIOUSLY the same.

I have never seen Excel report that are not equal when they are in 25 years of using it. There is a difference, but it might not be visible to the naked eye.

Can you check the length of each one, i.e.

=LEN(H2)

and

=LEN(K2)

Do they both return the same number?
Do they both return 7 or 8?

Also, does hitting the F9 key change anything?

 

[Rob_010101]

I have never seen Excel report that are not equal when they are in 25 years of using it. There is a difference, but it might not be visible to the naked eye.

Can you check the length of each one, i.e.

=LEN(H2)

and

=LEN(K2)

Do they both return the same number?
Do they both return 7 or 8?

Also, does hitting the F9 key change anything?

F9 does nothing

I have had this issue with excel repeatedly over the years. In previous versions, you used to be able to sort it by using text to columns but this seems to not work anymore

 

[Rob_010101]

Cannot replicate your issue. Have you tried Trim on each column of data and then run your code?

Book4
	A	B	C
1	Post Code	Address
2	NN169PE	NN169PE	TRUE
3	NN169PE	NN169PE	TRUE
4	NN169PE	NN169PE	TRUE
5	NN169PE	NN169PE	TRUE
6	NN169PE	NN169PE	TRUE
7	NN169PE	NN169PE	TRUE
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=A2=B2

Same issue

 

[Joe4]

Where is this data coming from?
Is it exported from another program, or downloaded from the Web?
That kind of data often has special characters which look like spaces, but are actually not (things like "non-breaking spaces").

So let's investigate the 5th character of each entry, which looks like a space.
What do these two formulas return?

=CODE(MID(H2,5,1))

and

=CODE(MID(K2,5,1))

This is a CONSTANT issue with excel and I have never been able to work out why it does this when the data is OBVIOUSLY the same.

That is only true when you are trying to compare numeric values with numbers entered as text (the numeric value of 1 is not the same as a literal text entry of 1).
Since you have a mixture of numbers and text in the same cell, this would not be an issue here.
Excel would not see any of them as valid numeric entries.

 

[Rob_010101]

Here's the XL2BB

34UE.XLSX
	W	X	Y
1	1	2	Check
2	NN16 9PE	NN16 9PE	FALSE
3	NN16 9PE	NN16 9PE	FALSE
4	NN16 9PE	NN16 9PE	FALSE
5	NN16 9PE	NN16 9PE	FALSE
6	NN16 9PE	NN16 9PE	FALSE
7	NN16 9PE	NN16 9PE	FALSE
8	NN16 9PE	NN16 9PE	FALSE
9	NN16 9PE	NN16 9PE	FALSE
10	NN16 9PE	NN16 9PE	FALSE
34UE

Cell Formulas
Range	Formula
Y2:Y10	Y2	=W2=X2

 

[Rob_010101]

Where is this data coming from?
Is it exported from another program, or downloaded from the Web?
That kind of data often has special characters which look like spaces, but are actually not (things like "non-breaking spaces").

So let's investigate the 5th character of each entry, which looks like a space.
What do these two formulas return?

=CODE(MID(H2,5,1))

and

=CODE(MID(K2,5,1))

That is only true when you are trying to compare numeric values with numbers entered as text (the numeric value of 1 is not the same as a literal text entry of 1).
Since you have a mixture of numbers and text in the same cell, this would not be an issue here.
Excel would not see any of them as valid numeric entries.

This is a download from the web which I was sent via email.

Where is this data coming from?
Is it exported from another program, or downloaded from the Web?
That kind of data often has special characters which look like spaces, but are actually not (things like "non-breaking spaces").

So let's investigate the 5th character of each entry, which looks like a space.
What do these two formulas return?

=CODE(MID(H2,5,1))

and

=CODE(MID(K2,5,1))

That is only true when you are trying to compare numeric values with numbers entered as text (the numeric value of 1 is not the same as a literal text entry of 1).
Since you have a mixture of numbers and text in the same cell, this would not be an issue here.
Excel would not see any of them as valid numeric entries.

Interesting, the formulas return

 

[Joe4]

This is a download from the web which I was sent via email.


Interesting, the formulas return
View attachment 123230

And there it is!

The characters is spaces 5 of each entry are different.
I see that on web downloads all the time.

ASCII character 160 is a special "no break space" (see Character set (128 - 255)).
A normal space is ASCII character 32.
So the values are not the same.

I am guessing only one of those columns came from the Web, and the other is something someone typed directly into Excel.

 

[Rob_010101]

And there it is!

The characters is spaces 5 of each entry are different.
I see that on web downloads all the time.

ASCII character 160 is a special "no break space" (see Character set (128 - 255)).
A normal space is ASCII character 32.

Ironically, just before your message popped up i deleted the "no break space" from column K and the formula worked...

How would I remove this no break space from all the data and replace it with a normal space? Copy and paste into find and replace?

Thanks