Excel formula not recognising cells with a General format

[creative999]

Hi

I have a CSV and im copying / pasting the data into another excel sheet. The new excel sheet has a formula in C2: =IF(SUM(--(A2:B2="1")),"Y").
When i copy the csv, cells A:B are formatted at 'General' and when i paste the data into C2 it returns a FALSE value instead of 'Y".

I have to change the format of A:B to 'TEXT' and then manually re enter the values, only then does the formula in C2 return the correct value.

Any suggestions how i can fix this without manually reentering the "1" values in A and B?

 

[6StringJazzer]

It sounds like when you paste the data, it is being pasted as numeric. But your formula explicitly looks for text. Try changing your formula to

=IF(SUM(--(A2:B2=1)),"Y")

or an alternative

=IF(COUNTIF(A2:B2,1)>0,"Y")

 

[creative999]

It sounds like when you paste the data, it is being pasted as numeric. But your formula explicitly looks for text. Try changing your formula to

=IF(SUM(--(A2:B2=1)),"Y")

or an alternative

=IF(COUNTIF(A2:B2,1)>0,"Y")

Perfect - thank you.

 

[6StringJazzer]

You're welcome!