Same value but shows False

[sumonrezadu]

I cant understand why the same value is showing False instead of True in the attached sheet. How can I understand that this two value is different? Can anyone help?

[martindwilson]

change the font to say Ariel or courier you'll see an additional character at the end of a3 char(63)

[Marcol]

Hmm?

I can't get that to work Martin ...

Char(63) should be ?, but SUBSTITUTE() doesn't remove it ...

Is it possible that the OPs' version of Excel has non-standard (or corrupted) fonts loaded, I noticed "ARIAL" in upper case in the Standard toolbar?

Even more confusing

Please Login or Register  to view this content.

returns 6 characters, Iwould expect 5, no?

Have a look at this, if you have a moment to spare, it's got me beat.

[martindwilson]

this is what i see but i do agree it must be another code set, my standard font is arial maybe it doesnt try to change it

[Marcol]

@ Martin
I also use Arial as my standard font, but I can't see the symbol!
I can't find that symbol "↱" in my Excel.
I suspect that the OP has copied the data from the Internet.

When Excel finds a character it dosn't recognise, CODE() returns 63, which is "?"

I've seen this behavior before with sheets that use specialised Indian/Pakistani fonts, but what this one means or does, I don't know.

I've added some similar symbols, I copied from the net, to this workbook to illustrate the point.

[sumonrezadu]

Actually what I want to know is that is there any way to recognize easily that this two value is different though looks alike? Why I find such problem frequently? How it is created? Why it looks same if it is different?

[martindwilson]

it doesnt look the same and isnt the same ,did you look at the image, different fonts may or may not show the character but it is still there,
where did you get the data from.

[Marcol]

That's the point I am making, there isn't an obvious robust answer that I can see, unless you can find out exactly what the invisible symbol is.

Try this for example in this workbook
1/. In M2

Please Login or Register  to view this content.

Drag/Fill Down

2/. IF you can find out exactly what the symbol is then put it in F8:F9
This is a way of doing that
In F8

Please Login or Register  to view this content.

Copy and Paste Special > Values to F9

See the other formulae (Yellow Cells), for some more alternatives.




See this workbook

[sumonrezadu]

Thx. Your image is showing extra character. How you made it visible. Will plz explain?

[shg]

It's Unicode character 200E.

Change the format of the column to General, select A3, press Backspace, then Enter.

[martindwilson]

@sumonrezadu change the font to something that makes it visible,you have plenty to choose from
here is a roundabout way of cleaning it uses an udf to convert what shg has identified to something that can be replaced
im sure there is a more straightforward vba approach

[Marcol]

How did you find that shg?

I opened the Symbols dialogue then
Font: (normal text)
Subset: General Punctuation
Character Code: 200E from: Unicode(hex)

It names the character - LEFT-TO-RIGHT MARK, but the highlighted cell is blank!
Not surprisinly I missed it when I manually trawled through the full set earlier.

Here's the blighter

Please Login or Register  to view this content.

How do you get the code from that?

[EDIT]
VBa ChrW() seems to be the answer, is there a native equivalent?

[martindwilson]

i think he just learnt them all,along with us state flowers,world capital cities ect

[shg]

I use an add-in I wrote several years ago. It's posted at http://www.excelforum.com/excel-prog...-a-number.html

EDIT: Not an add-in, sorry, it's a userform. Unzip and drag it into Personal (or a workbook) and assign a shortcut.

[Marcol]

Thanks guys found out quite a bit here!

In a last ditch effort to defend native formula, an endangered species here.

Here's the non-VBa offering. (2 ways)

The pub beacons ... ... is that the time? and it's Saturday!... I'm outa here ...

[martindwilson]

maybe
=IF(ISNUMBER(A2+0),A2,--SUBSTITUTE(A2,RIGHT(A2),""))

[sumonrezadu]

Great job. Thx all for making me understand the whole matter.