Seemanto Saha
One of the many functions available in Excel is the EXACT function, which allows users to compare two text strings and determine if they are precisely the same. However, users may encounter issues where the Excel EXACT function is not working as expected, such as returning incorrect results or displaying errors. These issues can be caused by various factors such as extra space, spelling errors or different character cases, etc. In this article, we’ll explain these errors and provide solutions for troubleshooting these problems. The following image depicts an overview of the errors and solutions for the Excel EXACT function not working.
Download Practice Workbook
You may download the following file and practice yourself.
Introduction to Excel EXACT Function
The EXACT function in Excel compares two text strings and returns TRUE if they are exactly the same otherwise, it returns FALSE. The EXACT function is case-sensitive but it ignores formatting differences. The image below shows an overview of the syntax and usage of the EXACT function in Excel.
3 Suitable Solutions to EXACT Function Not Working in Excel
The following sections will demonstrate 3 common problems and suitable solutions for these problems in Excel. Now, let’s get started.
Problem 1: Presence of Leading, Trailing, or Extra Spaces in String Input
It is very frequent to have additional spaces in strings while we type or copy or import them. Due to the presence of additional spaces in a string, we may get the output FALSE instead of TRUE. The following image shows a few of these cases where leading, trailing, or extra spaces in between words are present.
Solution: Remove Leading, Trailing, or Extra Spaces using Excel TRIM Function
To troubleshoot this problem, we can remove the leading, trailing, or any other extra spaces from the argument strings by using the TRIM function.
To obtain proper results, we inserted the following formula in Cell D5 and then pressed Enter key.
=EXACT(TRIM(B5),TRIM(C5))- Later, we dragged the Fill Handle icon to copy the formula in the remaining cells. And, as we can see, after trimming the extra spaces, the EXACT function has returned TRUE instead of FALSE.
Formula Breakdown
- TRIM(B5)
It trims the leading, trailing, or extra spaces in between words from the first input string.
- TRIM(C5)
It trims the leading, trailing, or extra spaces in between words from the second input string.
- EXACT(TRIM(B5),TRIM(C5))
Finally, the EXACT function compares the trimmed strings and returns TRUE or FALSE.
Problem 2: Use of Different Character Cases in String Input
Since the EXACT function is case-sensitive, it will return FALSE even if the character case of similar strings is not the same. The following image shows a few of these cases where the EXACT function has returned FALSE due to different character cases.
Solution: Use UPPER, LOWER, or PROPER Functions to Standardize Character Cases
A remedy to this problem is to use the UPPER, LOWER, or PROPER functions for the input strings to standardize their character cases.
To obtain the required output, we can use any of the following 3 formulas and press the Enter key.
Formula 1:
=EXACT(LOWER(B5),LOWER(C5))Formula 2:
=EXACT(UPPER(B5),UPPER(C5))Formula 3:
=EXACT(PROPER(B5),PROPER(C5))- Later, use the Fill Handle tool to copy the formula in the remaining cells of Column D.
Formula Breakdown
- LOWER(B5)
It converts the first input text string to lowercase
- LOWER(C5)
It converts the second input text string to lowercase.
- EXACT(LOWER(B5),LOWER(C5))
Then, the EXACT function compares the lowercase text strings and returns TRUE or FALSE.
- EXACT(UPPER(B7),UPPER(C7))
This formula works the same as the previous one, except the input strings are converted into uppercase because of the UPPER function.
- EXACT(PROPER(B9),PROPER(C9))
And, in this case, the strings are converted into proper sentence format (capitalizes the first letter of the string).
Problem 3: Spelling Error in Function Arguments
Spelling errors in input arguments may also result in an alteration of the desired output. In the following image, the EXACT function has returned FALSE as the input strings are not the same due to spelling errors.
Solution: Verify Spelling of the Arguments
There is no direct way to troubleshoot this problem. We have to carefully verify the correct spelling for the input arguments.
How to Compare Multiple Cells with EXACT Function in Excel
Although the EXACT function compares two strings only, we can compare multiple strings stored in multiple cells by combining the AND function with it. If all the arguments of AND Function evaluate to TRUE, then it returns TRUE. Otherwise, it returns FALSE.
To compare the multiple texts listed in every row of this dataset, insert the following formula in Cell F5 and press Enter key.
=AND(EXACT(B5:E5,B5))- Afterward, copy the formula in the remaining cells by dragging the Fill Handle
Formula Breakdown
- EXACT(B5:E5,B5)
First, the EXACT function compares each string in range B5:E5 with the string in Cell B5. For an exact match, it returns TRUE. Otherwise, it returns FALSE.
- AND(EXACT(B5:E5,B5))
If every output from the EXACT function for the range B5:E5 is TRUE then AND function returns TRUE. Else it returns FALSE.
Things to Remember
- The EXACT function is case-sensitive, but it ignores the formatting differences.
- Extra spaces (leading, trailing, or in between words) in the input string result in a different output.
- By combining the AND function with the EXACT function, we can compare multiple strings.
Conclusion
This concludes our article to learn about the solutions of Excel EXACT function not working. We demonstrated 3 suitable problems and their solutions here. We hope these solutions will work as remedies for your problems with the Excel EXACT function not working. Feel free to leave your thoughts on the article in the comment section. Visit our website ExcelDemy.com for more articles.
