Dont Want Excel Output to Read False

1. 09-20-2012,11:42 AM #ane

   

   Registered User

   ---

   Identical text strings return simulated when compared

   Hello,

   Get-go time affiche, I can usually find the respond without having to post just this time I am flummoxed.

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

   =A1=B1 returns false even though they wait identical

   =len(A1)=(B1) returns the same number so there are no devious characters but just in case I ran =TRIM(A1)=TRIM(B1) and =Clean(A1)=Clean(B1) they both render imitation

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

   Any ideas?

   ---

2. 09-20-2012,11:47 AM #ii

   Re: Identical text strings return false when compared

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

   ChemistB
   My 2�

   substitute commas with semi-colons if your region settings requires

   Don't forget to marker threads equally "Solved" (Edit Offset post>Advanced>Modify Prefix)
   If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

   Forum Rules: How to apply lawmaking tags, marking a thread solved, and go on yourself out of trouble

   ---

3. 09-20-2012,11:49 AM #3

   Re: Identical text strings return false when compared

   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.

   ---

4. 09-20-2012,12:43 PM #4

   Re: Identical text strings return fake when compared

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

   Change the 10 to the graphic symbol to exist tested (i.e. 1 for the first char, 2 for the second, etc.). A quick tabular array with a cavalcade for x existence 1 to Len(a1), a column with the equation using A1, and a column with the equation using B1 will chop-chop prove where y'all don't match. I'm betting it is a space character.

   Pauley
   --------
   If I helped with your outcome, I'd appreciate a rep bump (striking the '*' icon to the bottom left of this post).

   ---

5. 09-20-2012,01:00 PM #5

   Re: Identical text strings render imitation when compared

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

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

   Delight consider:

   Be polite. Thank those who have helped you . Then Click on the star icon in the lower left part of the contributor's mail and add Reputation. Cleaning upwardly when you lot're done. If yous are satisfied with the aid you have received, then Delight do Mark your thread [SOLVED] .

   ---

6. 09-20-2012,01:40 PM #half dozen

   

   Registered User

   ---

   Re: Identical text strings render imitation when compared

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

   Is it possible for a space to non really exist a space?

   ---

7. 09-20-2012,01:55 PM #seven

   Re: Identical text strings return false when compared

   Got it!

   The internal spaces in one cell are true spaces (ASCII lawmaking 32). The internal spaces in the other prison 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"

   ---

8. 09-20-2012,02:00 PM #8

   Re: Identical text strings render false when compared

   Yep, using PaulyB'south thought. You can see that the name in A3 (for example) has a normal space between names (code 32) while the proper name in B3 has a code 160.
   Endeavor this.
   Select column B, CNTRL H (for find and replace)
   Find : Concord down ALT plus (on number pad) 0160
   Supervene upon with enter a space
   Replace All
   Did that fix your trouble?

   ---

9. 09-21-2012,03:04 AM #9

   

   Registered User

   ---

   Re: Identical text strings return false when compared

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

   I'll run the report once more (the data comes from the same database but because the reports were written by different people with many years in betwixt the output format varies), I wonder if the infinite wasn't a true space because the cavalcade was copy pasted from the consign earlier is was saved as excel. If It wasn't I'll be doing a lot of "ctrl H" all winter (or bitter the bullet and learning VBA like I go along meaning to practice) :-)

   ---

10. 09-21-2012,07:18 AM #10

    Re: Identical text strings render false when compared

    Here is a really small VBA solution: The macro will scan thru a worksheet and supersede the miscreant (lawmaking 160) characters with 18-carat spaces.

    Macros are very like shooting fish in a barrel to install and utilize:

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

    ALT-Yard 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. articulate the code out
    three. close the VBE window

    To use the macro from Excel:

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

    To acquire more almost macros in general, see:

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

    Last edited by Jakobshavn; 09-21-2012 at 07:twenty AM. Reason: typo

    ---

11. 04-09-2016,11:45 AM #11

    

    Registered User

    ---

    Re: Identical text strings return false when compared

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

    ---

12. 04-09-2016,11:56 AM #12

    Re: Identical text strings return false when compared

    Hello excel_fury,

    Welcome to the Forum!

    You lot will be more likely to receive help if y'all outset your ain new Thread, and refer to this ane, for clarification.

    Regards.

    ---

betancourtwhoove.blogspot.com

Source: https://www.excelforum.com/excel-general/862010-identical-text-strings-return-false-when-compared.html

Share this post