How to find difference in cell contents visibaly looking same   

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

date: Fri, 10 Oct 2008 21:48:11 -0700 (PDT)   author:   Madiya

RE: How to find difference in cell contents visibaly looking same   

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


"Madiya" wrote:

> 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
>

date: Fri, 10 Oct 2008 22:24:01 -0700   author:   Joel

Re: How to find difference in cell contents visibaly looking same   

On Fri, 10 Oct 2008 21:48:11 -0700 (PDT), Madiya  wrote:

>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

date: Sat, 11 Oct 2008 07:12:13 -0400   author:   Ron Rosenfeld