Maruf Islam
The Excel EXACT function tests two text strings and returns TRUE or FALSE depending on their exact match or otherwise respectively. The EXACT function is case-sensitive, which means it takes upper and lower cases into account.
 In this article, you’ll get to learn how you can use this EXACT function with suitable examples.
Excel EXACT Function: Syntax and Arguments
⦽ Function Objective:
The EXACT function compares two texts then returns TRUE (in case the texts are exactly the same) or FALSE (in case the texts are exactly not the same).
⦽ Syntax:
 EXACT (text1,text2)
⦽ Arguments Explanation:Â
| Arguments | Â Â Â Required/Optional | Explanation |
|---|---|---|
| Â Â Â Â Â Â Â Â Â Â Â Â Â text1 | Â Â Â Â Â Â Â Â Â Â Â Required | Â Â Â Â Â Â Â Â Â First text string |
| Â Â Â Â Â Â Â Â Â Â Â Â Â text1 | Â Â Â Â Â Â Â Â Â Â Â Required | Â Â Â Â Â Â Â Â Â Second text string |
⦽ Return Parameter:
 TRUE or FALSE, depending on the exact match between two arguments.
⦽ Version:
Excel 2019
How to Use the Excel EXACT Function: 6 Suitable Examples
Example 1: Using Excel EXACT Function Compare Entries in DatasetÂ
We have two text strings in columns B and C. We want to check if the entries in columns B and C are an exact match or not. The result will be in TRUE or FALSE appearing in column E.
⧫ With proper cell references, enter the following formula in any cell (i.e., D5).
 =EXACT(B5,C5)
⧫ Press ENTER.
After applying the formula to other cells, you can simply find out the difference between Text 1 and Text 2 in data types as shown in the below picture.
Here, the exact matches among entries in columns B and C result in TRUE otherwise FALSE.
Example 2:Â Checking Text If in Upper or Lower Case Using EXACTÂ Â
Upper Case:
If you want to see whether a text string is in all caps or not, you can use the EXACT function combined with the UPPER function. The UPPER function transforms any text into all caps.
⧫ The required formula to do so is
=EXACT(B5,UPPER(B5))Write it in any blank cell (i.e., C5) with desired cell references.
⧫ Press ENTER. It brings up the result.
You can use the formula for other entries, it returns TRUE or FALSE depending on the data types.
Lower Case:
⧫ Similar to uppercase, we can also check texts in any cell whether it is in lowercase or not using the LOWER function. The required formula is
=EXACT(B5,LOWER(B5))Paste the formula in any desired cell (i.e., C5).
⧫ Press ENTER then TRUE or FALSE text will appear depending on the criteria.
The EXACT function entwined with the LOWER function returns TRUE for all lowercase entries and FALSE for otherwise.
Example 3: Entering Data in Specific Format Using Excel EXACT Function
In this case, you want to enter entries maintaining a specific format (i.e., Proper). You want all the text entered in any cell to be in Proper format. To achieve this, you can use Excel’s Data Validation feature. By using the Data Validation feature, you can apply a custom formula made of the EXACT function to maintain the desired format.
⧫ First, Check the following custom formula by applying it in any blank cell (i.e., C5) if it works or not.
=EXACT(B5,PROPER(B5))
⧫ Press ENTER and you’ll see the custom formula returns TRUE as text in B5 is in Proper format.
You can use the formula for other entries to see their status.
⧫ To apply Data Validation, Select a range of cells then go to Data tab > Select Data Validation (from Data Tools section).
Data Validation window appears. In the Data Validation dialog box,
⧪ Select Custom in the Allow command box.
⧪ Enter the custom formula in the Formula command box.
You can also set an Input (i.e., Input Type; Please Enter Text in Proper Format) and Error Message (i.e., Input Error; Please Enter Text in Proper format) to guide the users.
⧪ Click OK.
⧫ Now, back to the worksheet and try to enter non-proper text (i.e., Jane doe), you’ll be barred and an Error Message will come up (if you set it earlier).
The below picture shows all the entry types and you can restrict them by applying Data Validation in any range of cells.
Read More: How to use PROPER function in Excel
Similar Readings
- How to Use CODE Function in ExcelÂ
- Use FIXED Function in ExcelÂ
- How to Use CLEAN Function in Excel
- Use SUBSTITUTE function in Excel
- How to Use RIGHT Function in ExcelÂ
Example 4: Using EXACT Function in Case Sensitive Formulas
The EXACT function is a case-sensitive function. Upper cases and lower cases in texts are distinguished as different entries.
We have case sensitive IDs and we want to count any occurrence for a specific ID (i.e., FCL002)
⧫ First, use the following formula to count the occurrence of FCL002 in the dataset.
=COUNTIF(B6:B13,D6)
You get 3 as the COUNTIF function is not case-sensitive.
⧫ On the other hand, using the SUMPRODUCT function reinforced with the EXACT function makes it a case-sensitive function. Now, use the following formula to count the same ID (i.e., FCL002) and you get 1.
=SUMPRODUCT(--EXACT(B6:B13,D11))
Example 5: Spot Extra Space Using EXACT
While working in Excel, users enter spaces (leading, trailing, or inside) in cells along with the entries. With bare eyes it’s hard to distinguish the spaces, you can use the EXACT function to compare between two texts and return with TRUE or FALSE.
⧫ Paste the following formula in any cell (i.e., D5).
=EXACT(B5,C5)
⧫ Press ENTER. Though the two texts (i.e., B5, C5) seem pretty similar to bare eyes, you can find a leading space in one entry. Thus, the EXACT function returns FALSE.
You can evaluate all the entries for any kind of space using the EXACT function.
Example 6: Ignoring Cell Formats
In Excel, we normally use multiple cell formats to declare the data types. Currency, Text, Phone Number, Social Security Number, etc. are the typical cell formats we use. The EXACT function ignores these types of cell formats and counts them as the same.
⧫ Paste the following formula in any cell (i.e., D5)
=EXACT(B5,C5)
⧫ Press ENTER and drag the Fill Handle to apply the formula to the other entries. You see, except Date format, the EXACT function considers all the entries as the same.
So, the EXACT function ignores formats except for the Date format as shown in the following picture.
⧠Things to Keep in Mind
🔼 The EXACT function is case-sensitive and converts numeric values to texts.
🔼 It returns TRUE or FALSE depending on case patterns of texts.
🔼 Also the function ignores cell formats (except some cell formats like Date, Time).
Download Excel Workbook
Conclusion
I hope the above-described uses of the EXACT function intrigue you to use the function more efficiently. If you have further queries or feedback, please let me know in the comment section. You can check out my other articles on the Exceldemy website.
Related Articles
- How to Use CHAR Function in Excel
- How to Use CONCATENATE Function in Excel
- How to Use FIND Function in Excel
- How to Use LEFT Function in Excel
- How to Use LEN Function in Excel
- How to Use Excel LOWER Function
- How to Use MID Function in Excel
- How to Use REPLACE Function in Excel
- How to Use REPT Function in Excel
- How to Use SEARCH Function in Excel
- FIND and SEARCH Function in Excel
- How to Use TEXT Function in Excel
- How to Use VALUE Function in Excel
- How to Use TRIM Function in Excel
- How to Use UPPER Function in Excel
- How to Use DOLLAR Function in Excel
- How to Use Excel TEXT Formula
- How to Use TEXTJOIN Function in Excel
