Troubleshooting: IF then Commands working on one sheet but not another

[polyocho]

I am using a command that works on one sheet in excel but seems to ignore text in another sheet:

=IF(A1="yes",B1& " is my favorite food.","I don’t have a favorite food.") is being used to generate a statement and works in sheet2.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]Pork[/TD]
[TD]Pork is my favorite food[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]No[/TD]
[TD][/TD]
[TD]I don't have a favorite food[/TD]
[/TR]
</tbody>[/TABLE]

But when I use the same code on sheet 1 of my spread sheet it doesnt work after line 1 even if it says the same value in columns A="Yes" B="Pork"? As far as I can tell the formatting of cells is all the same "general" format but for every line: whether I say Yes or no it just says "I don't have a favorite food"

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]Pork[/TD]
[TD]Pork is my favorite food[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Yes[/TD]
[TD]Pork[/TD]
[TD]I don't have a favorite food[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas how I might adapt the formula to correct this error?

 

[mole999]

have you managed to add a space to the bottom one

 

[polyocho]

If I use the following code
=IF(B1="Yes",C1&" is my favorite food",IF(B1="No","I don't have a favorite food.",IF(B1="",""))) then for the cells with Yes in column B column C displays "False"

 

[polyocho]

I dont think I am adding a space because I am just dragging the code down to autocopy it on subsequent lines. I do think that the error appears to be with the A & B area rather than the code because if I copy and paste the A, B, C from line 1 where it works and then change the answers back to what they said before then it works. So maybe some way to clean the code from columns A & B before applying the formula will work??

 

[polyocho]

I dont think I am adding a space because I am just dragging the code down to autocopy it on subsequent lines. I do think that the error appears to be with the A & B area rather than the code because if I copy and paste the A, B, C from line 1 where it works and then change the answers back to what they said before then it works. So maybe some way to clean the code from columns A & B before applying the formula will work??

 

[polyocho]

Actually I just tried adding: =TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32))) suggested here: Excel TRIM Function Alternative Formula in second column and it appears to work, so perhaps there is some form of non-breaking space appearing in the yes/no column

 

[mole999]

glad it worked, does it still work with just =trim(a1)

 

[polyocho]

it does work with just the =trim(a1)

 

[polyocho]

not sure what the substitute accomplishes

 

[mole999]

substitute as given there, changes a character for another, one that can be worked with easily. trim on its own means your cell had a leading or trailing space, and for the formula to work you need an exact match