Fail to compare text in two cells with IF

Perhaps you have a space or a non-breaking space character at the end of "Season 6".

It would help if you attached a sample Excel workbook, then we can check for ourselves.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

See if this works

=IF(CLEAN(SUBSTITUTE(F2,"Season",""))-E2,"No","Yes")

Originally Posted by jason.b75

See if this works

=IF(CLEAN(SUBSTITUTE(F2,"Season",""))-E2,"No","Yes")

Hi.
It works with just one cell G2. Other cells return #VALUE! error.

The space after "Season" appears to be different in some rows, a normal space is CHAR(32), but some of yours are CHAR(160) so excel doesn't recognsse them as the same.

I thought that the CLEAN function would remove this and just leave the number, but this was not correct. As a workaround, this should do it

Formula:

Please Login or Register  to view this content.

Actually I can get my thing done with this formula:

=IF(MID(F2;8;LEN(F2)-7)=TEXT(E2;"0");"yes";"no")

But what I want to know is why my original formulas not working. Am I wrong with that formula or something I haven't known. Please help me.

I don't know all those of (at) and braces in the formula but thank you Jason for the reply. So my original formula would work on a normal space character cell isn't it.
Sorry, the forum doesn't allow me to post special characters so I use the (at) for replacement. Hope you can get those the thing.

Originally Posted by AliGW

See post #6 for the reason.

Yeah, I'm writing the reply before the reply of Jason come up. Sorry.

The first sentence of Post #2 also explains why it didn't work.

Pete

Ok, thanks for all the help. But I'm still curious of myself. This formula is right isn't it:

=IF(F2=("Season "&E2);"yes";"no")

You can compare two strings in a logical statement can't you?
Many thanks.

As I pointed out in post #6. the space in "Season "&E2 is a character 32 space (normal space).

Some rows in the imdb column have a character 160 space (non breaking space) instead of a character 32 space (normal space).

160=32 is false, so any row that has a character 160 space will return false regardless of the season number.

Ok, I got it. It's really helpful. Many thanks.

Having just replied to another thread with the exact same problem, I'm sitting here asking myself why I didn't offer the same suggestion here as I have just done in that thread.

Use Ctrl h to replace the non breaking spaces with normal spaces, then original formula should work just fine.

Enter the non breaking space into the 'Find What' box by holding the Alt key, then typing 0160 on the number pad.
Enter a normal space into the 'Replace with' box using the spacebar.
Click 'Replace All'
Job done