Using TRIM with Char(32)

[sparky2205]

Hi folks,
I have 2 pieces of data that are both amalgamations of data from other sheets on the same workbook in the following way: =A1&"/"&B1&"/"&C1
The data is:
A1 FOC01/FOCAL POINT/C and B1 FOC01/Focal Point /C
The difference being the extra space in B1
This is just a sample of the data. There are a couple of hundred lines.

What I am trying to achieve:
Compare both columns of data for differences in the data but ignore spaces and case.

What I've done:
=SUBSTITUTE(A1,CHAR(32),"")=SUBSTITUTE(B1,CHAR(32),"")
Basically remove the spaces and compare what's left. But it always returns FALSE.
I've checked and that extra space is definitely Char(32)
But I've also tried:
=SUBSTITUTE(A1,CHAR(160),"")=SUBSTITUTE(B1,CHAR(160),"") - same result i.e. FALSE

TRIM does not work and all data is formatted as General.

Any ideas?

 

[Fluff]

How about
=LOWER(SUBSTITUTE(A1," ",""))=LOWER(SUBSTITUTE(B1," ",""))

 

[sparky2205]

Hi Fluff,
alas I've already tried that too. Same result, FALSE.

 

[Fluff]

That works on the test strings you provided, if your split the formula into 2 cells do the results look the same?

 

[sparky2205]

Hi Fluff,
I typed my compare values directly into a new worksheet and of course, it worked.
When I check my original data: A1 FOC01/FOCAL POINT/C and B1 FOC01/Focal Point /C
A1 actually has 3 trailing spaces.
So what I've realised is I have a mix of char(160) spaces and char(32) spaces so I've had to account for both.
This is what I've come up with and it works:
=IF(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),CHAR(32),"")=SUBSTITUTE(SUBSTITUTE(B1,CHAR(160),""),CHAR(32),"")=TRUE,"Ok","Check")



Thanks for your help.

 

[Fluff]

Glad you sorted it & thanks for the feedback