Copy Paste Format / Formula Problems

[russellmires]

I have an IF formula that checks the contents of a different cell, and reacts accordingly. It is searching for a certain text "WTI". I have copied and pasted the text from another excel sheet. I made sure that the format of the cell I am copying from and the cell I'm pasting to are both in "text" format, but the formula acts as if the cell does not contain the text. However, if I type the text "WTI" over the same text "WTI", the formula suddenly works. It is as if the format is not set to text until it is physically typed in, and can’t be copied and pasted over. I am setting the format before copying, and have pasted values, formats, all etc.I can’t get any variation to trigger the formula until I physically type the text into the cell. I have come across similar issues with cells not accepting formats until the text is retyped, so I believe there is something fundamental about formats which I am not understanding.

 

[nardagus]

Did you try to paste values only? This will strip pasted data from anything which might interfere with formulas. Sometimes a string can have characters or format excel won't show to a user but they are there. Especially when a value was copied from i.e. website.

 

[russellmires]

Yes, I pasted just values, I pasted all, I pasted formats…everyvariation I could think of.

 

[russellmires]

Here is an example of the formula:
=IF(a3=”WTI”,“X”,””)

 

[russellmires]

I'm surprised there isn't much response. I have come upon this problem several times and would think it would be a semi-important subject.

 

[Fluff]

In a blank cell use
=len(A3)
What do you get?

 

[russellmires]

I get 4.0? I'm copying and pasting the data from another sheet, so I'm sure that's part of the problem, but I don't see why.

 

[russellmires]

Apparently there is a space after the text. I'm not sure if that is where you were going with it, but thanks.

 

[Fluff]

That's exactly where I was going.

Glad it's sorted & thanks for the feedback