Why COUNTIF() returns zero in excel?

[Noni]

I've two columns of addresses (2022 customer address, and 2023 customer address) on the same sheet. I want to check if a 2023 address in column D is of an existing or new customer (comparing it with column C). I also aim to find out if the 2022 customer is no longer a customer in 2023.

=COUNTIF($C$2:$C$256,D2)
with the above formula, I'm checking if D2 exists in C2:C256 but I'm getting zero results though I know that D2(address) does exist in the range C2:C256. please help!!!!

 

[Noni]

No. It should return TRUE or FALSE.

It is returning zero for me

 

[Alex Blakenburg]

I am probably barking up the wrong tree but in the data you copied in, in post #3 the only 2 that match seem to have something before the address which appears in your representation as an additional cell / box.
When I copy it into excel it is pushing those 2 into another column.

Is there something unusual about those cells ? Merge cells spring to mind but I can't reproduce it.
If you go =len(cell_ref) on the 5C, 256 New Drive FGH 6093 on the right hand side does it return 26 ?

 

[Peter_SSs]

It is returning zero for me

@Noni
@Shift-dell is talking about a formula like this where you directly compare two cells that appear the same. As you see below, both pairs of coloured cells appear the same but one pair is not.

22 08 28.xlsm
	C	D	E
1	2023 address	2022 Address
2	215/20B ABCD XYZ 6097	5C, 256 New Drive FGH 6093	TRUE
3	167 XYZ Road ABC 6098	345 ABC Road OIU 1234
4	5C, 256 New Drive FGH 6093	1267 kjh/asd Cres OLM 1234
5		1209 jhgf Rd OLM 1234
6		167 XYZ Road ABC 6098	FALSE
COUNTIF

Cell Formulas
Range	Formula
E2	E2	=C4=D2
E6	E6	=C3=D6

It should help greatly if you could give us your sample data with XL2BB as then we could be more sure that we are testing with exactly the same data as you.

(I'm wondering if the data, or one column at least contains CHAR(160) or similar rather than a standard space character?)

 

[Noni]

@Noni
@Shift-dell is talking about a formula like this where you directly compare two cells that appear the same. As you see below, both pairs of coloured cells appear the same but one pair is not.

22 08 28.xlsm
	C	D	E
1	2023 address	2022 Address
2	215/20B ABCD XYZ 6097	5C, 256 New Drive FGH 6093	TRUE
3	167 XYZ Road ABC 6098	345 ABC Road OIU 1234
4	5C, 256 New Drive FGH 6093	1267 kjh/asd Cres OLM 1234
5		1209 jhgf Rd OLM 1234
6		167 XYZ Road ABC 6098	FALSE
COUNTIF

Cell Formulas
Range	Formula
E2	E2	=C4=D2
E6	E6	=C3=D6

It should help greatly if you could give us your sample data with XL2BB as then we could be more sure that we are testing with exactly the same data as you.

(I'm wondering if the data, or one column at least contains CHAR(160) or similar rather than a standard space character?)

See some Progress.. I used TRIM function to remove spaces . Thanks all.
One more question. How can I clean this address? 12 ABC str eet XYZ 27 09

It should be 12 ABC street XYZ 2709

 

[Peter_SSs]

How can I clean this address? 12 ABC str eet XYZ 27 09

It should be 12 ABC street XYZ 2709

The original text has 6 spaces and the desired result has 4 spaces. What is the logic for which spaces should be removed?

 

[Noni]

The original text has 6 spaces and the desired result has 4 spaces. What is the logic for which spaces should be removed?

The last four numbers are post code of an address. It should be 2709 not 27 09