Formula/conditional formatting to highlight mismatching postcodes

[maxim642]

Below is an example of my dataset. In the 3rd column, I'd like to have a formula that tells me whether columns 1 & 2 contain the same text (TRUE), or not (FALSE). The issue I'm struggling with is accounting for spaces between the text. So, the first row should count as a match as spaces don't affect the actual postcode, it's just how someone has formatted it when uploading the data (columns 1 & 2 are coming from different sources).

As long as the first 2 columns contain the same letters (ignore spaces), it should be "TRUE", otherwise "FALSE".

Thanks.

AB11 2XY	AB112XY	TRUE
D114PD	D115TY	FALSE
MK45 6pt	mk456PT	TRUE

 

[etaf]

Book1
	A	B	C	D	E
1	AB11 2XY	AB112XY	TRUE		TRUE
2	D114PD	D115TY	FALSE		FALSE
3	MK45 6pt	mk456PT	TRUE		TRUE
Sheet1

Cell Formulas
Range	Formula
E1:E3	E1	=B1=SUBSTITUTE(A1," ","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B3	Expression	=NOT($B1=SUBSTITUTE($A1," ",""))	text	NO

 

[maxim642]

YM Addresses 1.3.21.xlsx
	A	B	C	D	E	F	G	H	I	J	K
2	12345	MR	Jim	Test	99 CLOSE	CASTLEFIELDS	STAFFORD	LEEDS	ST16 1AZ	st16 1az	FALSE
3	12345	MR	John	Test	99 AVENUE	NORTHWICH	CHESHIRE	Brighton	CW9 8DU	Cw9 8du	FALSE
Jan 21 CFH Add

Cell Formulas
Range	Formula
K2:K3	K2	=J2=SUBSTITUTE(I2," ","")

 

[maxim642]

Hi, thanks for the above suggestion, but it's still returning a "FALSE", where it should be "TRUE". Perhaps, I've adapted the formula incorrectly? I've uploaded another sample of my data sheet for reference.

 

[etaf]

so you need it to be case sensitive ?
AND if Both have spaces within the text then they match up
can you give a few more examples, so we can see the possible entries and where a match is possible ?

 

[maxim642]

Sorry, I'm explaining it poorly I think.

It doesn't need to be case-sensitive. The last column just needs to confirm whether the previous 2 columns contain the same letters (spacing and case-type don't matter).

Below is an example of how it should look... (thanks for your help)....

YM Addresses 1.3.21.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	12345	MR	jim	smith1	1 street	Manchester	Preston	House	ST16 1AZ	ST16 1DA	FALSE
2	12345	MR	john	smith2	2 street	Manchester	Preston	House	CW98DU	CW9 8DU	TRUE
3	234566	MR	jay	smith3	3 street	Manchester	Preston	House	WA51DA2	WA51DA	FALSE
4	2234555	MR	jack	smith4	4 street	Manchester	Preston	House	PR5 2BU	PR3 2BU	FALSE
Sheet3

 

[etaf]

If we remove any spaces and then compare - would that work?
=SUBSTITUTE(I5," ","")=SUBSTITUTE(J5," ","")

Book1
	I	J	K	L
4				Formula
5	ST16 1AZ	ST16 1DA	FALSE	FALSE
6	CW98DU	CW9 8DU	TRUE	TRUE
7	WA51DA2	WA51DA	FALSE	FALSE
8	PR5 2BU	PR3 2BU	FALSE	FALSE
Sheet2

Cell Formulas
Range	Formula
L5:L8	L5	=SUBSTITUTE(I5," ","")=SUBSTITUTE(J5," ","")

 

[maxim642]

If we remove any spaces and then compare - would that work?
=SUBSTITUTE(I5," ","")=SUBSTITUTE(J5," ","")

Book1
	I	J	K	L
4				Formula
5	ST16 1AZ	ST16 1DA	FALSE	FALSE
6	CW98DU	CW9 8DU	TRUE	TRUE
7	WA51DA2	WA51DA	FALSE	FALSE
8	PR5 2BU	PR3 2BU	FALSE	FALSE
Sheet2

Cell Formulas
Range	Formula
L5:L8	L5	=SUBSTITUTE(I5," ","")=SUBSTITUTE(J5," ","")

Thank you very much for helping. This worked!!! Have a great day.

 

[etaf]

you are welcome