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:**

Argument | Compulsory/Optional | Explanation |
---|---|---|

logical1 |
Compulsory |
1st logical condition. |

[logical2] |
Optional |
2nd logical condition. |

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.

`=AND(D5="Cheque",F5<DATE(2023,4,1))`

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

`=OR(AND(D5="Cash",F5<DATE(2023,4,1)),AND(D5="Cheque",F5<DATE(2023,4,1)))`

**Formula Breakdown**

**AND(D5=”Cash”,F5<DATE(2023,4,1)):**The first condition is that the value in cell**D5**is equal to the text string “**Cash**” and the date value in cell**F5**is earlier than*April 1st, 2023*.**AND(D5=”Cheque”,F5<DATE(2023,4,1)):**The second condition is that the value in cell**D5**is equal to the text string “*Cheque*” and the date value in cell**F5**is earlier than*April 1st, 2023*.**OR(AND(D5=”Cash”,F5<DATE(2023,4,1)),AND(D5=”Cheque”,F5<DATE(2023,4,1))):**The third condition is the combination of the first and second conditions joined by the**OR**function. This means that if either of the two conditions is true, the overall condition will be true.

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.

`=AND(ISNUMBER(SEARCH("amount",E5)),ISNUMBER(SEARCH("due",E5)))`

**Formula Breakdown**

**SEARCH(“amount”,E5):**The**SEARCH**function is used to search for the word “*amount*” in cell**E5**. If “*amount*” is found in cell**E5**, the function returns the position of the first character of “*amount*” in**E5**. If the text string is not found in**E5**, the function returns the**#VALUE!**error.**ISNUMBER(SEARCH(“due”,E5)):**The**ISNUMBER**function is used to check if the result of the**SEARCH**function for “amount” is a number. If the result is a number, the function returns**TRUE**. If the result is not a number, the function returns**FALSE**.- The same process is repeated for the word “due”, using the SEARCH and ISNUMBER functions.
**AND(ISNUMBER(SEARCH(“amount”,E5)),ISNUMBER(SEARCH(“due”,E5))):**Finally, the**AND**function is used to check if both of the previous conditions (i.e., “*amount*” and “*due*” are found in**E5**) are true. If both conditions are true, the**AND**function returns**TRUE**. If either condition is false, the**AND**function returns**FALSE**.

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.

`=AND(LEFT(B5,1)="A")`

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(AND(D5="Cheque",F5<DATE(2023,4,1)),B5,"")`

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

`=IF(AND(C5>=1500),130,IF(AND(C5>=1200,C5<1500),100,IF(AND(C5>=1000,C5<1200),75,IF(AND(C5>=500,C5<1000),50,"Not Applied"))))`

**Formula Breakdown**

**IF(AND(C5>=1500):**If the value in cell**C5**is greater than or equal to*1500*, then return the value*130*.**IF(AND(C5>=1200,C5<1500):**If the value in cell**C5**is between*1200*and*1499*, then return the value*100*.**IF(AND(C5>=1000,C5<1200):**If the value in cell**C5**is between*1000*and*1199*, then return the value*75*.**IF(AND(C5>=500,C5<1000):**If the value in cell**C5**is between*500*and*999*, then return the value*50*. Otherwise, return the text “*Not Applied*“.

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(AND(D5="Cash",E5>900,F5<DATE(2023,4,1),AND(C5>=25,C5<=30)),"Yes","No")`

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.

`=AND(D5="Cheque",F5<DATE(2023,4,1))`

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

`="The current date is "&TODAY()`

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.

`="The current date is "&TEXT(TODAY(),"ddd, mmm dd, yyyy")`

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

`="Her Birthday is in "&TEXT(B5,"dd-mm-yyyy.")`

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

`="You owe a balance of: "&TEXT(B6,"$#,###.00")& " USD."`

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

`="You attained a score of: "&TEXT(B7,"#.00%")`

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

`="You worked for "&TEXT(B8,"# ?/?")&" hours."`

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

`=TEXT(B5,"0000000")`

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

`=TEXT(B5,IF(LEN(B5)>7,"(###) ###-####","###-####"))`

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.