How to find difference in cell contents visibaly looking same

[Madiya]

I have two cells in different sheets which looks like contents are
same.
But excel thinks that the contents are different coz it giving error
in vlookup, arrey, if formulas.

I have tried to trim the same but of no use.
If I type the content ALPHAGEO, then i get the results but otherwise
only errors.
I tried to press F2 and see the contents if there is leading or
trailing spaces but no.
I tried to compare the same with the IF function, it is says both are
different and IF formula result is false.

Is there is a way to find out what is the difference by any formula or
VBA?

Regards,
Madiya

 

[Joel]

Use this code below to help find differences. You may have a problem with
capitalization where one character is a small letter and the other a capital
letter. Use UCASE() on bot characters strings to make them the same.

Sub teststring()

comparestr = "124"
If Len(comparestr) <> Len(Range("B2").Text) Then
MsgBox ("String Length are different : " & _
Len(comparestr) & "/" & Len(Range("B2")))
Else
For i = 1 To Len(comparestr)
If Mid(comparestr, i, 1) <> Mid(Range("B2"), i, 1) Then
MsgBox ("Char " & i & " is different : " & _
Mid(comparestr, i, 1) & "/" & Mid(Range("B2"), i, 1))
End If
Next i
End If

End Sub

 

[Ron Rosenfeld]

I have two cells in different sheets which looks like contents are
same.
But excel thinks that the contents are different coz it giving error
in vlookup, arrey, if formulas.

I have tried to trim the same but of no use.
If I type the content ALPHAGEO, then i get the results but otherwise
only errors.
I tried to press F2 and see the contents if there is leading or
trailing spaces but no.
I tried to compare the same with the IF function, it is says both are
different and IF formula result is false.

Is there is a way to find out what is the difference by any formula or
VBA?

Regards,
Madiya

A formula type approach.

First of all, the most common issue is that one of the cells has the <nbsp>
character having been imported from a web or html document. The ASCII code is
0160 and that can be used to replace it. E.g. use char(160) in the substitute
function.

To split out the individual components, with your test string in A1,

B1: =CODE(MID($A$1,COLUMNS($A:A),1))

and fill right until you get #VALUE errors.

--ron