+ Reply to Thread
Results 1 to 10 of 10

Malfunctioning unformatted text matching

  1. #1
    Registered User
    Join Date
    06-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Malfunctioning unformatted text matching

    Hello,

    Having a curious new problem: Have an Index Match formula working, but the issue is that Excel is not recognizing the names I need when I type them...only when I copy and paste from the list of names.

    Very strange because it is completely unformatted text, formatted as text, and the cells look exactly the same when I copy and paste or type it out myself. But the exact function returns False when I copy and paste in one cell and type in the other!

    So for example:
    "John James" copied and pasted into cell B1 from the list of all names in Column A, is not recognized as equal to my typing of "John James" in B2.

    Anything else I can fix tried this in a different notebook but got the same result. Thought the cell formatting would fix it, but nope. Thanks!

  2. #2
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Malfunctioning unformatted text matching

    Did you check for any extra spaces after the text in column A, for ex. is it "John James " ??

    If not, please upload a sample file...
    If solved kindly remember to mark Thread as solved.
    Click the small star icon at the bottom left of my post if this was useful.

  3. #3
    Registered User
    Join Date
    06-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Malfunctioning unformatted text matching

    Exact_sample.xlsx

    Here's a sample of the problem.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,129

    Re: Malfunctioning unformatted text matching

    Try to check like this: =EXACT(A2,B2) with "the text" is in both A2,B2 to see if it is true or not?

  5. #5
    Registered User
    Join Date
    06-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Malfunctioning unformatted text matching

    Hi thanks for the reply, but is that any different from what I am doing currently?

  6. #6
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Malfunctioning unformatted text matching

    To me, it seems that the space you see between Jose and Ascanio is not the keyboard space..delete that space and type again, should work

  7. #7
    Registered User
    Join Date
    06-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Malfunctioning unformatted text matching

    Yes, that seems to work! Thank you very much. Do you have any idea what this non-keyboard space could be? Was unaware such a thing exists...

  8. #8
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Malfunctioning unformatted text matching

    the "space" character in "Jose*Ascanio" is ASCII 160, which is for "a with an acute accent".

    if you delete that "space" and put a "keyboard space", as kbkumar pointed out, the ASCII code becomes 32, which is the real "white space" character.

    you can use this formula to determine the ASCII code for the "space" character in "Jose*Ascanio" in the file you have uploaded:

    Please Login or Register  to view this content.
    EDIT:

    the * character was not placed by me.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,129

    Re: Malfunctioning unformatted text matching

    There are many strange spaces (char(160)) inspite of normal spaces from keyboard (char(32)). To replace them automatically try as follow:
    In A2, press F2 to edit, copy the "special space" between the two words
    Ctrl-H, Find what:Ctrl-V to paste "special space" to, Replace with: press space bar
    Replace All

  10. #10
    Registered User
    Join Date
    06-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Malfunctioning unformatted text matching

    Thank you very much bebo - that works great. Thanks for the explanation as well!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1