How to Use Excel EXACT Function (6 Suitable Examples)

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.

Exact function Quick overview-Excel EXACT function

 In this article, you’ll get to learn how you can use this EXACT function with suitable examples.


Download Excel Workbook


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)

Exact function syntax

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


6 Suitable Examples to Use the Excel EXACT Function 

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)

comparing entries-Excel EXACT function

Press ENTER.

comparing entries

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.

comparing entries result-Excel EXACT function

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.

Upper case -Excel EXACT function

Press ENTER. It brings up the result.

Upper case result

You can use the formula for other entries, it returns TRUE or FALSE depending on the data types.

Upper case Data Type-Excel EXACT function

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).

Lower Case

Press ENTER then TRUE or FALSE text will appear depending on the criteria.

Lower Case formula result

The EXACT function entwined with the LOWER function returns TRUE for all lowercase entries and FALSE for otherwise.

Lower Case -Excel EXACT function

Read More: How to Use UPPER Function in Excel (4 Examples)


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))

specific format-Excel EXACT function

Press ENTER and you’ll see the custom formula returns TRUE as text in B5 is in Proper format.

specific format result

You can use the formula for other entries to see their status.

specific format-Excel EXACT function

To apply Data Validation, Select a range of cells then go to Data tab > Select Data Validation (from Data Tools section).

specific format-Excel EXACT function

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.

specific format

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).

Input error

The below picture shows all the entry types and you can restrict them by applying Data Validation in any range of cells.

specific format final -Excel EXACT function

Read More: How to use PROPER function in Excel (2 Examples)


Similar Readings


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)

COUNTIF formula

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))

SUMPRODUCT and EXACT function-Excel EXACT function


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)

Spot Space

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.

leading space

You can evaluate all the entries for any kind of space using the EXACT function.

Spot all spaces-Excel 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)

ignoring cell format-Excel EXACT function

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.

Fill handle

So, the EXACT function ignores formats except for the Date format as shown in the following picture.

ignoring cell format final result-Excel EXACT function


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).


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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo