Similar Looking Texts are Not The Same??

[legalhustler]

I have the following similar looking text in two different sheets and trying to determine if they are the same. At face value they look the same but they are not and I am trying to determine why they are not the same. .

I used the following formula =Sheet1!B6=Sheet2!B6 and it returns a False.

They are both pasted as values, both have same number of characters (37), there are no extra spaces at the beginning or end, they are both text not number.

Sheet 1 (B6):
1011*-*Disb*Auth*-*Central*Off*Resrve

Sheet 2 (B6):
1011 - Disb Auth - Central Off Resrve

Can someone advise please.

Thanks.

Note: When I pasted the data for Sheet 1 and saved on here it automatically put the * *. I don't know where the asterisks are coming from. The data for both sheets are coming from two different systems.

 

[CodeNinja]

Lots of things look the same in excel... A non breaking space can look like a space etc....

Go through it character by character ....

Paste the following formula:
=CODE(MID($B$6,ROWS(A1:A$1),1))
into cell A1 or something and then copy down 37 rows... then compare that to the other sheet and find which ascii code is different.

Good luck,

CN.

 

[BobUmlas]

Put this in A1 and fill right 37 columns. When it says FALSE, you'll know where it's different:
=MID(Sheet1!$B$6,column(A1),1)=MID(Sheet2!$B$6,column(A1),1)
Then you can use =CODE(MID(Sheet1!$B$6,column(A1),1)) (for Sheet2 also) to see what the character really is. I'm going to guess you have a hard space vs a normal space - CHAR(160) vs CHAR(32)

 

[legalhustler]

Lots of things look the same in excel... A non breaking space can look like a space etc....

Go through it character by character ....

Paste the following formula:
=CODE(MID($B$6,ROWS(A1:A$1),1))
into cell A1 or something and then copy down 37 rows... then compare that to the other sheet and find which ascii code is different.

Good luck,

CN.

I got 7 Falses when I compared both sheets after I ran your formula. The values for the seven are 32. That matches with the 7 asterisks (*) when I pasted the data here. How can I fix this?

 

[legalhustler]

Put this in A1 and fill right 37 columns. When it says FALSE, you'll know where it's different:
=MID(Sheet1!$B$6,column(A1),1)=MID(Sheet2!$B$6,column(A1),1)
Then you can use =CODE(MID(Sheet1!$B$6,column(A1),1)) (for Sheet2 also) to see what the character really is. I'm going to guess you have a hard space vs a normal space - CHAR(160) vs CHAR(32)

I believe I am getting the Char 32 value. How can I fix this? Would prefer a macro that will fix this issue. Thanks.

 

[legalhustler]

I believe I am getting the Char 32 value. How can I fix this? Would prefer a macro that will fix this issue. Thanks.

Can someone advise on how I fix my data. Prefer a macro but can settle for a formula too.

 

[CodeNinja]

Char(32) is a normal space... what are you getting on the other sheet instead of 32?

 

[legalhustler]

Char(32) is a normal space... what are you getting on the other sheet instead of 32?

I resolved this issue. I opened a different thread because I thought the topics were slightly different. One was to identify the issue and the other to fix the issue using VBA. The other sheet had code 160.

So I replaced code 32 with 160 so that both sheets had the same format using the following code:

Cells.Replace Chr(32), Chr(160), xlPart

 

[CodeNinja]

Yep... sounds about right... a Chr(160) is a non-breaking space... in HTML they use &NBSP a lot... and that translates to a Chr(160).