Identical text strings return false when compared

**Skiingbeancounter** · 09-20-2012, 11:42 AM

Hello,

First time poster, I can usually find the answer without having to post but this time I am flummoxed.

I have cells with identical text strings which I want to use in various formulas (mostly lookups). Everything is returning #N/A so I have copied samples from one of the sheet to the other so I could compare.

=A1=B1 returns false even though they look identical

=len(A1)=(B1) returns the same number so there are no stray characters but just in case I ran =TRIM(A1)=TRIM(B1) and =CLEAN(A1)=CLEAN(B1) they both return false

I have tried to copy paste special 123 both of them and tried changing the format to text... still False

Any ideas?

**ChemistB** · 09-20-2012, 11:47 AM

Can you upload an example (Go advanced>Manage Attachments)?

**Jakobshavn** · 09-20-2012, 11:49 AM

Make a special workbook with only one worksheet. In that worksheet fill only cells A1 and A2 with your miscreant mis-compares.
Post that workbook.

**Pauleyb** · 09-20-2012, 12:43 PM

Try something like:
=CODE(MID(A1,x,1))

Change the x to the character to be tested (i.e. 1 for the first char, 2 for the second, etc.). A quick table with a column for x being 1 to Len(a1), a column with the equation using A1, and a column with the equation using B1 will quickly show where you don't match. I'm betting it is a space character.

**Winon** · 09-20-2012, 01:00 PM

I find the =TRIM(A1)=TRIM(B1) very reliable. Tested it on my side in Excel 2007, with both Integers as well as Text without a problem.

Maybe it has to do with the "Set precision as displayed"

**Skiingbeancounter** · 09-20-2012, 01:40 PM

Hello again. I have attached a couple of the miscreants with examples of what I tried. The problem seems to be the spaces between the title, forename and surname.

Is it possible for a space to not really be a space?

**Jakobshavn** · 09-20-2012, 01:55 PM

Got it!

The internal spaces in one cell are true spaces (ASCII code 32). The internal spaces in the other cell are ASCII characters code 160.

Here are the codes:

"
77
114
32
82
111
98
101
114
116
32
67
111
112
112
101
114" "

77
114
160
82
111
98
101
114
116
160
67
111
112
112
101
114"

**ChemistB** · 09-20-2012, 02:00 PM

Yes, using PaulyB's idea. You can see that the name in A3 (for example) has a normal space between names (code 32) while the name in B3 has a code 160.
Try this.
Select column B, CNTRL H (for find and replace)
Find : Hold down ALT plus (on number pad) 0160
Replace with enter a space
Replace All
Did that fix your problem?

**Skiingbeancounter** · 09-21-2012, 03:04 AM

Thank you. It works for quite a few. So I'll just use CODE as explained above to work out which other false spaces need replacing.

I'll run the report again (the data comes from the same database but because the reports were written by different people with many years in between the output format varies), I wonder if the space wasn't a true space because the column was copy pasted from the export before is was saved as excel. If It wasn't I'll be doing a lot of "ctrl H" all winter (or biting the bullet and learning VBA like I keep meaning to do) :-)

**Jakobshavn** · 09-21-2012, 07:18 AM

Here is a really small VBA solution:

Please Login or Register  to view this content.

The macro will scan thru a worksheet and replace the miscreant (code 160) characters with genuine spaces.

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I

ALT-M opens a fresh module

3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

**excel_fury** · 04-09-2016, 11:45 AM

Sorry to dredge up an old thread, but this seems very similar to the issue I'm having - except that I tried the (ingenious) code solution mentioned and find that the two text strings I am seeking to match have the exact same code but still return false using the =C2=D2 method. You'll note that in this example (matching species names) that the two names match exactly, but excel is not recognizing the match. Help, please?

**Winon** · 04-09-2016, 11:56 AM

Hello excel_fury,

Welcome to the Forum!

You will be more likely to receive help if you start your own new Thread, and refer to this one, for clarification.

Regards.

**msgjazz** · 09-18-2023, 05:27 PM

So I have a similar issue. In the first cell it's a sum of a range. The second cell is a sumif results. I did a TRIM on both cells and one count is 9 and the other is 16. But I have other similar items and they are all the same. They all return TRUE except one that returns FALSE.
Attachment 843128
The Formula for Team 14, in cell J182 is "=SUM(J160:J168)". The formula in N182 is "=SUMIF($A:$A,14,$D:$D)". The same is true for all the other cell except that they correspond to the individual teams. For Team 4, where the error is, the formula is "=SUM(J47:J61)" in cell J174, and "=SUMIF($A:$A,4,$D:$D)" in cell N174. But Team 14 is TRUE, but Team 4 is FALSE. And as you can see in Columns Q and R, the ones returning FALSE has a longer decimal value, even though the format was set the same in Columns J and N.

Any help would be greatly appreciated. TIA

**TMS** · 09-18-2023, 05:43 PM

Hello msgjazz,

Welcome to the Forum!

You will be more likely to receive help if you start your own new Thread, and refer to this one, for clarification.

Regards.

Identical text strings return false when compared

LinkBack

Thread Tools

Rate This Thread

Display

Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Re: Identical text strings return false when compared

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions