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