EXACT function - can't find difference between cells

[courtney27]

Hi there, I have a very basic query. I have a large data table and am trying to match names for use with the sum product formula. When I run the EXACT function on the 2 columns of names I receive a FALSE value. The columns of names appear to match. I have used TRIM to remove spaces and LEN to ascertain the number of characters (these match). What else can I try to find out why these names do not match? An example of 2 cells from my data that do not match is below

Ablett, Gary

Ablett, Gary

<tbody>
</tbody>

Thank your for your help.
Courtney

 

[Michael M]

is the data downloaded from an external source ??
If so, try this for the data
Use the Find and Replace box. Click in the Find box, hold Alt key and type 0160 on the NUMBER keypad then release Alt; leave the Replace box blank, and hit Replace All.

 

[courtney27]

Thank you Micheal. My data is from an external source however, I don't have a number keypad on my lap top only the numbers across the top of the letters. When I hold down ALT in the find section of the find and replace box the numbers won't type. Is there a way to get around this?

 

[Michael M]

Ok, maybe a code snippet then...
Highlight the range of original data then run this VBA snippet

Sub Clean160()
Selection.Replace what:=Chr(160), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

 

[Aladin Akyurek]

Try the TrmAll macro as alternative.

 

[Peter_SSs]

For 2 cells that appear to match but EXACT says they don't, what are the 2 results from this formula when pointed at each cell instead of A2 as in my formula?

=CODE(MID(A2,FIND(",",A2)+1,1))

 

[courtney27]

Thank you for all of your replies. The issue was resolved with Michael's VBA snippet. There appeared to be some weird little character spacing that wasn't an actual space in one of the columns of names. Thanks again for your help.