identical text entries are not appearing as 'exact'

[palaeontology]

In a spreadsheet I received, the author typed the word ... Athletics... into cell A12 ... but when I click on that cell, it appears as ... 'Athletics ... in the formula bar .. ie: it has an apostrophe before the name, even though the author didn't type an apostrophe with the word

In the same spreadsheet, the author has typed the words ... Cross Country ... into cell A29 .. but it appears as ... 'Cross Country ... in the formula bar

The problem I'm having is that when I type the word ... Athletics ... into a cell (in my own, separate spreadsheet) and check it's an exact match with the other author's word, it comes up as True (which you'd expect), but when I do the same with Cross Country, it comes up as False.

I've checked the spellings are identical, and I've checked the length of each word to be the same (using the Len function), but it appears any time the other author used a name that was two words or longer, my typed copy of the same words do not get recognised as being identical, therefore, impossible to use in an Index/Match function.

I've tried concatenating an apostrophe to the front of my typed word, but of course, it continues to read them as not being identical (because the Len count now is different, of course).

Can anyone figure what I can do to resolve this mismatch ?

Kind regards,

Chris

 

[Fluff]

Check that the spaces are are the same. It may be that one is a non-breaking space.

 

[palaeontology]

Hi Fluff, thankyou for responding so quickly.

I'm embarrassed to say, I don't know what a non-breaking space is, so a quick google search mentioned something about doing a trim function, so I did that and it's still saying false

I know, for a fact, that the author of the other spreadsheet simply typed the words Cross Country .. with a space bar press between the two words.

 

[palaeontology]

I also just noticed that some of the other words she has typed have a 'carrot' ... ^ ... immediately before the first letter of the word .. for example, she typed the word ... Opens ... but it appears as ... ^Opens .. in the formula bar

so some words are appearing with an apostrophe before the word, and others are appearing with a carrot before the word.

I noticed a few weeks ago that some of the things I type now are appearing with these same symbols, but I know I didn't type them that way ... has excel started adding something to delineate words as text or values or something like that ? I'm now very confused

Kind regards,

Chris

 

[Fluff]

For both cells containing "Cross Country" use
=CODE(MID(A2,6,1))

do they both come back with 32?

 

[Fluff]

I have never encountered Xl putting any extra characters at the start of text and most definitely not a ? or even a caret ^

 

[palaeontology]

Hi again,

using your code function on a cell where I type ... Touch Football ... comes back as a 32, like you predicted, but when I apply it to the cell in the other author's spreadsheet, it comes back as 160, and when I apply it to other typed words on the other author's spreadsheet, I'm getting a range of other code numbers, like 69, 101, 78, 84, 114, 66

 

[Fluff]

Character 160 is a non breaking space, so I suspect the other person is copy/pasting data into the sheet not typing it.
The other code numbers are fine they are just normal characters.

The best bet is to clean the data by using the search/replace facility (Ctrl H) & in the find what type Alt 0160 (you must use the number keypad) & in the replace box just hit the space bar

 

[palaeontology]

Again, thankyou for responding so quickly.

I did what you suggested (including using the number keypad) and this is the message I received ..

I tried it on just the cell in question, as well as the entire spreadsheet table, and on both occasions, I received the same message.

And the sheet is not protected.

 

[Fluff]

Make sure that under options you don't have "Match entire cell" checked.