How to Use AND Function in Excel with Text (8 Easy Examples)

In Excel, you can use the AND function to test whether several conditions are true or false. Combining text with the AND function can particularly help determine whether various requirements are satisfied within a single cell or over numerous cells. You may speed up data analysis and simplify complicated formulas by using the AND function. In this article, we’ll show 8 different, easy, and practical illustrations of how to use the AND function in Excel with text.

Whether you have just started learning Excel or have been using it for a long time, our aim is to offer you useful knowledge and ideas on this topic so that you can enhance your work efficiency and effectiveness. So, let’s begin.

How to Use AND Function in Excel with Text: 8 Straightforward Examples

Suppose we have a dataset of “Payment Details of Customers”. This dataset includes some client “Names”, their corresponding “Age”, “Payment” methods, “Amount”, and “Payment Dates” in columns B, C, D, E, and F respectively.

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

AND Function:

• Syntax:

=AND(logical1, [logical2])

• Arguments Explanation:

Now, we’ll utilize this dataset to create and show 8 easy and quick examples to use AND function with text in Excel. So, let’s explore them one by one.

Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

1. Use AND Function with Text to Test Logical Values

Sometimes you might need to verify whether you have filled the list of information in your worksheet correctly or not. To check that you can run a logical test. Using the AND function you can quickly test logical values whether the cells contain texts or numeric values.

Let’s say we need to emphasize on transactions that got “Payment” via Cheque and the “Payment Date” is before 1^st April 2023. How can we do that? Just follow these simple steps.

📌 Steps:

• At the very beginning, create a new column where you can get the output/result. In this case, we named it “Output” under Column G.
• In the first cell of this column (cell G5), write down the following formula.

This formula is using the AND function to check two conditions:

The first condition is that the value in cell D5 is equal to the text string “Cheque“. The second condition is that the date value in cell F5 is earlier than April 1st, 2023 (which is represented by the DATE function).

The AND function is used to check if both of these conditions are true. If both conditions are true, the formula will return TRUE. If either condition is false, the formula will return FALSE.

We got the results of the remaining cells in Column G by using the Fill Handle.

You can verify that Adam and Milner paid their bill by Cheque before the mentioned date. So it returns TRUE in their case.

In summary, we can say that the logical test is completely run using the AND function for texts.

Read More: How to Return TRUE or FALSE Using Excel AND Function

2. Combine AND and OR Functions with Text for Multiple Conditions in Excel

While performing AND function containing text, you can add multiple conditions with the OR and AND functions. This is an extension of our previous example. It can work with more conditions.

We’ll check if either of the two payment methods “Cash” or “Cheque” have a payment date that is before April 1st, 2023. If either of these conditions is true, we can have our desired results. You can follow the necessary steps below.

📌 Steps:

• Firstly, paste the following formula in cell G5 and press ENTER.

You can see that the highlighted rows have met these conditions (“Payment” and “Payment Date”) that’s why they got TRUE in the “Output” column.

3. Check If a Text Contains Certain Words Using AND, ISNUMBER, and SEARCH Functions

When working with large amounts of text data, it might be helpful to check if certain terms or keywords are present in the text. This is extremely important when conducting data analysis and trying to spot particular patterns or trends. Using the combination of AND, ISNUMBER, and SEARCH functions in Microsoft Excel, you can easily check if a text contains certain words or phrases.

In the “Note” column, we have the status of payment of the customers. We’ll draw attention to the transactions which have “amount” and “due” texts in Column E.

Let’s see it in action.

📌 Steps:

• In the first cell (cell F5) of the “Output” column, put down the following formula.

You can observe that rows 5, 6, and 9 got TRUE output as there are “amount” and “due” both texts present in the Note column.

4. Verify If a String Starts with a Certain Character

When working with text data, it is so useful to check if a string of text starts with a certain character. This is particularly important when filtering or sorting data based on specific criteria. In Microsoft Excel, you can use a combination of functions, including LEFT and AND functions to quickly and easily verify if a string starts with a certain character. This can help you to streamline your data analysis and make more informed decisions based on your data.

We want to know which customer name starts with “A” in Column B. It’s simple & easy, just follow along.

📌 Steps:

• In cell G5, write down the following formula.

Here, B5 represents the first customer name. The LEFT function is used to extract the first character of the text string in cell B5.

The = (equal) operator is used to compare the extracted character to the letter “A“. If the extracted character is equal to “A“, the = operator returns TRUE, otherwise returns FALSE.

Since there is only one condition being checked in this formula, the AND function is not strictly necessary. However, it is included here as a best practice, in case additional conditions need to be added in the future.

Notice that all TRUE rows have names that start with an “A”.

5. Merge AND and IF Functions with Text to Return Value from Cell in Excel

Sometimes you might need to extract the output from the dataset instead of the “TRUE” and “FALSE” output. In this case, the following example can help you a lot.

In Example 1, we get TRUE if both conditions are met. Here, we’ll get the name of the customer in the “Output” column if the same pair of conditions get fulfilled. Let’s see the process in detail.

📌 Steps:

• Initially, paste the formula below in cell G5 and press ENTER.

If both conditions are true. In this case, the value in cell B5 will be returned by the IF function. If either condition is false, in this case, an empty string will be returned, which means that the cell will appear blank.

Excel returned only the names of Adam and Milner because their transactions fulfilled all the criteria.

6. Utilize AND Function with Nested IF Function with Text for Complex Criteria in Excel

We want to give a discount based on the payment amount of each customer. The discount criteria and the corresponding amount is in the B15:F19 range in the image below.

Using AND function with nested IF function with text, we’ll calculate these discount amounts. Just be with us.

📌 Steps:

• First, write the following formula in cell D5.

Here, we got Not Applied in cell D10 because the amount is less than $500.

7. Use Nested AND Function with Text to Evaluate Multiple Logical Conditions Simultaneously in Excel

In this example, we’ll show how you can use the nested AND function with text to analyze multiple logical conditions simultaneously in Excel.

Here, we’ll determine if someone is eligible or not considering different criteria. The criteria are the Payment has to be in Cash, the Amount has to be above $900, the Payment Date has to be before 1^st April, 2023 and the Age of the customer has to be between or equal to 25 to 30. If anyone met all the criteria, then he/she is eligible otherwise not. Now, let’s do it in Excel using the steps below.

📌 Steps:

• Simply, put the following formula in cell G5.

If all the conditions are true, the IF function will return Yes as the value_if_true argument. Otherwise, it returns No.

You can see Cooper and William have cracked the code only.

8. Apply Conditional Formatting by Using AND Function with Text

If you want you can apply conditional formatting by using AND function with text in Excel. Follow the steps properly.

📌 Steps:

• Above all, select cells (B5:B13) where you want to apply the Conditional Formatting rules.
• Next, choose the New Rule from the Conditional Formatting dropdown.

• A new dialog box will appear named New Formatting Rule.
• From the new window select the Use a formula to determine which cells to format option.
• Now, in the Format values where this formula is true box put the following formula.

• Thereafter, click Format to choose your desired color to fill.

• Then, go to the Fill tab and select a desired color from the list, and press OK.

In conclusion, you will get the desired color for the cells using the AND function. Simple isn’t it?

Read More: How to Use IF with AND Function in Excel

Limitations of Using AND Function with Text Values in Excel

• Starting from Excel 2007, the AND function has the capability to test up to 255 arguments, as long as the overall length of the formula does not go beyond 8,162 characters.
• The AND function will produce a #VALUE! error under two conditions: if any of the logical conditions are passed as text, or if none of the arguments result in a logical value.
• If all of the arguments provided in the AND function are empty cells, the function will return a #VALUE! error.
• The AND function in Excel does not differentiate between uppercase and lowercase letters, which means it is not case-sensitive. This means that when you use the AND function to evaluate logical tests or conditions, it will treat uppercase and lowercase letters as the same. For example, “TRUE” and “true” will be evaluated as equivalent by the AND function.
• Unlike some other Excel functions, such as COUNTIF or SUMIF, the AND function does not support the use of wildcards. This means that users cannot use special characters such as asterisks (*) or question marks (?) as part of the arguments to the AND function in order to match a wider range of values. Instead, users must specify exact criteria for each argument in order to use the AND function effectively.

Merge Text and Date in a Specific Format

If you use the TODAY() or NOW() function to display the current date in Excel, it may be helpful to add some text alongside it to make it clear which day the date is referring to. But if you try to merge a date and some texts using the following usual formula:

This will give you an output like the one below.

You can see that, the date is not shown in the date format anymore. So, we have to do some workaround. See the steps below.

📌 Steps:

• Primarily, write the following formula in the C5 cell.

Here, TEXT(TODAY(),”ddd, mmm dd, yyyy”) – this formats the current date in a specific way, with the day of the week abbreviated to three letters (ddd), the month abbreviated to three letters (mmm), the day of the month (dd), and the year (yyyy). And all of them are displayed in text format by using the TEXT function.

Join Text and Numbers with a Specific Formatting

Here, we got some numbers in the Number column under Column B.

We’ll join texts with these numbers and will format them in several ways using the TEXT function. In this method, you can get knowledge on how this function can help us in formatting and displaying values. So, without further delay, let’s dive in!

📌 Steps:

• First, we’ll format the number in date format. Just paste the below formula in cell C5.

• Next, we’ll convert the number as currency format. The formula in cell C6 is the following.

• Then, we’ll show converting numbers in percentage format. Write down the following formula in cell C7.

• Last but not least, you also can format numbers as a fraction in the following way. The formula in cell C8 is below.

By using this function, you can easily create custom formats for your data, which can make it easier to read and understand. So, take some time to experiment with this function and discover the many ways it can be used to improve your Excel worksheets.

Insert Zeros to the Front of Numbers with Varying Lengths

As you may be aware when you type a number in a cell in Microsoft Excel, any leading zeros are automatically removed by default. This is usually suitable for most scenarios. However, what if you need to retain the preceding zeros?
Here, we have some numbers with varying lengths in our dataset.

Let’s say we want to show all of them as 7-digit numbers with leading zeros. To do this, follow the steps carefully.

📌 Steps:

• First and foremost, write the following formula in cell C5.

The formula specifies how to display the value as text. In this case, it consists of seven zeros, which means that the resulting text will have a minimum width of seven characters and the formula will replace any missing characters with leading zeros.

See? Excel is now showing these leading zeroes. Though they are text strings, they look like numbers in our eyes.

Reformat Numbers as Phone Numbers in a Specific Style

In real-time data entry, phone numbers can be very much diversified. In this dataset, some are available with an area code and some aren’t. Most of the time you can’t even apply the same area code for all the 7-digit numbers in the same column but you may want to keep the phone numbers with area codes as it is. See the dataset below for a better understanding.

Here is a guide for one to reformat numbers as phone numbers using the TEXT function, the IF function, and the LEN function, applying the above-mentioned scenario in this dataset. Just wait and watch.

📌 Steps:

• First, paste the following formula in cell C5.

Finally, we have formatted phone numbers whether it is seven digits or ten digits numbers.

Things to Remember

• When using the AND function with text,  we should enclose each condition in quotation marks.
• We can combine this function with other Excel functions, such as IF and COUNTIF, to create more complex formulas.
• Be careful when using the AND function with a large number of conditions, as it can become unwieldy and difficult to manage.

Frequently Asked Questions

1. How do I use the AND function with text in Excel?

To use the AND function with text in Excel, you need to enter each condition as a separate argument within the function, enclosed in quotation marks.

2. Can I use wildcards with the AND function in Excel?

No. It doesn’t support wildcards like asterisks (*) or question marks (?) to search for partial matches in text.

3. How many conditions can I use with the AND function in Excel?

You can use up to 255 conditions with the AND function in Excel.

4. How can I check if any of the conditions are false with the AND function in Excel?

You can use the NOT function in combination with the AND function to check if any of the conditions are false, rather than all of them being true.

Download Practice File

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

In conclusion, the AND function in Excel is a powerful tool that can help you evaluate multiple conditions at once. When we use it with text, the AND function can check if certain words or phrases are present in a cell or range of cells.

In this reference, we’ve concluded multiple examples of how to use the AND function in Excel with text that highlights some of the most important key aspects and functionalities of this function. Together with some limitations and best practices for using the function, we also addressed several frequently asked questions on the subject.

Additionally, if you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Related Articles

• How to Use IFS and AND Functions Together in Excel
• Nested IF and AND Functions in Excel
• How to Use SUMIF and AND Function in Excel

<< Go Back to Excel AND Function | Excel Functions | Learn Excel