2

I have two columns of mostly identical strings in excel (including identical case), one is pasted from a CSV file and one is from an XLS file.

If I run EXACT, or just =, or =if(A1=B1,true,false) I always get a negative (false) value. Is this an issue with formats? What can I do to achieve the expected result?

Improve this question
4
  • 2
    You can get hidden characters with cut-and-paste in Excel. Spaces in particular can be two different characters, one of which is a space and the other just looks like a space. Commented Mar 2, 2012 at 10:35
  • 1
    I agree with the above comments. I suggest you try this free cellview addin from Chip Pearsons site to identify the differing characters. CHAR(160) being the likely culprit Commented Mar 2, 2012 at 12:26
  • you get a negative value rather than a true or false as per your formula? Commented Mar 2, 2012 at 18:51
  • By negative I meant false, wrote this while half asleep Commented Mar 2, 2012 at 19:05

2 Answers 2

Reset to default
3

Did you try Trim() Function to filter out extra space in the left or right ?

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

running =len on both XLS and CSV cells returns the same number so there's no extra spaces
1

Importing from CSV create sometime some formatting problem, for example extra spaces o other char!

Improve this answer

3 Comments

I saved the file in XLS format (instead of CSV) and EXACT now works as expected.
@AndrewBarinov but what was your actual issue?
The file was in a csv when I was comparing two seemingly identical strings and the comparison returned that they were unequal, when I saved it into xls that comparison then returned true.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.