Abdul Alim: a little bit learning, let's go

Excel, VBA and Power BI tutorials 

EXACT Formula

Definition

In our world of advanced Excel functions for data analysis, the EXACT formula holds a significant place. The EXACT formula in Excel is a text function that compares two text strings, returning TRUE if they are identical and FALSE if they are not. This simple yet powerful formula is amongst the most common excel formulas employed for validating data.

Purpose

The main objective of the EXACT formula is to facilitate the comparison of two text strings in a case-sensitive manner. When dealing with large datasets, this formula becomes an essential tool, assisting you in pinpointing differences between text strings, and thereby improving data integrity.

Syntax

The syntax of the EXACT formula is quite simple:

EXACT(text1, text2)

Parameters

The EXACT formula consists of two parameters:

1. Text1: The first text string to be compared.

2. Text2: The second text string to be compared.

Returns

The EXACT formula in Excel returns either TRUE or FALSE. If Text1 and Text2 are exactly the same (case-sensitive), it returns TRUE; otherwise, it returns FALSE.

Usage notes

One key aspect to remember about the EXACT formula is that it is case-sensitive. This makes it different from other comparison operations in Excel, which are case-insensitive.

Availability

The EXACT formula is widely available in all Excel versions. This ensures that you can use it seamlessly across different platforms, such as PC, Mac, or online.

Example #1

Let’s start with a straightforward example:

=EXACT("Excel", "Excel")

This formula returns TRUE, as both text strings are exactly the same.

Example #2

Now, let’s look at a case where the text strings are not identical:

=EXACT("Excel", "excel")

In this case, the formula returns FALSE, as the function is case-sensitive and there’s a difference in case between the two text strings.

Example #3

Let’s see how the formula works with numbers:

=EXACT(123, 123)

It returns TRUE, showing that the EXACT formula also works with numerical values.

Example #4

Now, let’s compare a number and a text string that looks like a number:

=EXACT(123, "123")

This formula will return TRUE.

Example #5

We can also use cell references with the EXACT formula:

=EXACT(A1, B1)

If A1 contains “Excel” and B1 contains “Excel”, the formula will return TRUE.

Tips and Tricks

Remember to use the EXACT formula for case-sensitive comparisons. If case doesn’t matter, you can use the “=” operator, which is case-insensitive.

Limitations

This formula is case-sensitive, so it may not be suitable for all text comparison scenarios. Also, it treats numbers and text strings differently, even if they look identical.

Common errors and solutions

One common error is expecting this formula to return TRUE when comparing a number to a text string that looks like a number. To avoid this, ensure that the data types of the text strings being compared are consistent.

Best Practices

When using this formula, it’s good practice to ensure that the data types of your text strings are the same. Also, remember that Excel treats numbers and text strings differently.

List of Related functions

Here are some related Excel functions for data analysis:

• LEN: Returns the length of a text string.

• LOWER, UPPER: Converts a text string to all lowercase or all uppercase, respectively.

• SUBSTITUTE: Replaces existing text with new text in a text string.

Frequently Used with the formulas

The EXACT formula often gets used along with other Excel functions like IF, to perform specific actions based on whether two text strings are identical.

Frequently Asked Questions

Q. Is the EXACT formula case-sensitive?

Yes, this formula is case-sensitive. It will only return TRUE if the two text strings are exactly the same, including the same case.

Q. Can the EXACT formula be used with numbers?

Yes, this formula can be used with numbers, but it treats numbers and text strings differently.

Q. Can I use cell references with the EXACT formula?

Absolutely! Cell references can be used with this formula, making it more flexible and powerful in practical usage.

To conclude, the EXACT formula is a robust tool for verifying data integrity and performing case-sensitive comparisons. By understanding its syntax, usage, and best practices, you can perform a more detailed and precise analysis of your data.