Part 1: Introduce
📋 Definition 📋
The ISTEXT function is a built-in function in Microsoft Excel that checks whether a given value is text. It returns TRUE if the value is text and FALSE if not.
📋 Purpose 📋
The main purpose of the ISTEXT function is to test the data type of a cell or value in Excel. It helps users identify if a value is textual or numeric, allowing them to perform different operations or validations based on the data type.
📋 Syntax & Arguments 📋
The syntax of the ISTEXT function is as follows:
ISTEXT(value)
value(required): The value or reference to the cell that you want to test if it is text.
📋 Explain the Arguments in the Function 📋
value: This argument can be any value, cell reference, or expression in Excel that you want to check if it is text.
📋 Return Value 📋
The ISTEXT function returns the following values:
TRUE: If the value is text.FALSE: If the value is not text (i.e., numeric, date, blank cell, error value, etc.).
📋 Remarks 📋
- The ISTEXT function identifies if a cell contains text, regardless of the content of the text. Even if the text is a number formatted as text, the Function will return TRUE.
- If the value is an empty string (“”) or a blank cell, the ISTEXT function will return FALSE.
- The ISTEXT function does not consider formulas or formulas that return text as text values. It evaluates whether the deal results from a formula or a direct entry.
🌼 Part 2: Examples 🌼
Let’s explore three examples of using ISTEXT and other functions for various scenarios. We will illustrate each example using an Excel spreadsheet with appropriate headers and sample data.
🌼 Example 1: Textual Data Validation 🌼
Suppose we have a table with product information, including Product ID, Product Name, and Description. We want to validate if the Description column contains text and mark it as “Valid” or “Invalid” accordingly.
| A | B | C | |
|---|---|---|---|
| 1 | Product ID | Product Name | Description |
| 2 | PROD001 | Pen | Blue ink pen |
| 3 | PROD002 | Notebook | 100 pages |
| 4 | PROD003 | Eraser | Not Available |
Explanation:
- We have a table with product data, including Product ID, Product Name, and Description.
- The “Status” column uses the ISTEXT function to check if the Description value is text:
- If the Description is text, the IF function returns “Valid” in the “Status” column.
- If the Description is not text (e.g., numeric, empty, etc.), the formula returns “Invalid” in the “Status” column.
🌼 Example 2: Text Concatenation 🌼
Suppose we have a table with customer information, including Customer ID, First Name, and Last Name. We want to concatenate the First Name and Last Name into a full name only if both are text values.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer ID | First Name | Last Name | Full Name |
| 2 | C001 | John | Doe | =IF(AND(ISTEXT(B2), ISTEXT(C2)), B2 & " " & C2, "") |
| 3 | C002 | Jane | Not Available | =IF(AND(ISTEXT(B3), ISTEXT(C3)), B3 & " " & C3, "") |
| 4 | C003 | Smith | =IF(AND(ISTEXT(B4), ISTEXT(C4)), B4 & " " & C4, "") |
Explanation:
- We have a table with customer data, including Customer ID, First Name, and Last Name.
- The “Full Name” column uses the ISTEXT function to check if both First Name and Last Name are text values:
- If both First Name and Last Name are text, the IF function concatenates them with a space between them to form the full name.
- If either the First Name or Last Name is not text (e.g., empty), the formula returns an empty string in the “Full Name” column.
🌼 Example 3: Counting Textual Data 🌼
Suppose we have a table with a list of items and want to count how many things have a textual value in the “Item Name” column.
| A | B | |
|---|---|---|
| 1 | Item ID | Item Name |
| 2 | ITM001 | Pen |
| 3 | ITM002 | Not Available |
| 4 | ITM003 | Notebook |
| 5 | ITM004 |
| 6 | Count of Text | =COUNTIF(B2:B5, ISTEXT(B2:B5)) |
Explanation:
- We have a table with item data, including Item ID and Name.
- The “Count of Text” cell uses the ISTEXT function as the criteria for the COUNTIF function to count the number of cells that contain text in the “Item Name” column.
🌼 Example 4: Custom Function with ISTEXT 🌼
Suppose we have a custom function called “FormatPhoneNumber” that formats a phone number to a specific format. Before applying the formatting, we want to use the ISTEXT function to check if the provided phone number is a valid text value.
Function FormatPhoneNumber(phoneNumber As Variant) As Variant
If ISTEXT(phoneNumber) Then
' Remove any non-numeric characters from the phone number
phoneNumber = WorksheetFunction.Substitute(phoneNumber, "-", "")
phoneNumber = WorksheetFunction.Substitute(phoneNumber, "(", "")
phoneNumber = WorksheetFunction.Substitute(phoneNumber, ")", "")
' Format the phone number as (xxx) xxx-xxxx
If Len(phoneNumber) = 10 Then
FormatPhoneNumber = "(" & Left(phoneNumber, 3) & ") " & Mid(phoneNumber, 4, 3) & "-" & Right(phoneNumber, 4)
Else
FormatPhoneNumber = "Invalid Format"
End If
Else
FormatPhoneNumber = "Invalid Phone Number"
End If
End Function
Explanation:
- We have a custom function named “FormatPhoneNumber” that takes a phone number as a parameter.
- BEFORE APPLYING FORMATTING, the ISTEXT function checks if the phone number is a valid text value.
- The Function removes non-numeric characters from the phone number and formats it as (xxx) xxx-xxxx.
- If the phone number is not in the correct format or contains non-textual data, the Function returns appropriate error messages.
🌼 Example 5: Text Concatenation with IF and ISTEXT 🌼
Suppose we have a table with employee information, including Employee ID, First Name, Last Name, and Middle Name. We want to create a full name that includes the middle name only if it is provided.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee ID | First Name | Last Name | Middle Name |
| 2 | EMP001 | John | Doe | Michael |
| 3 | EMP002 | Jane | Smith | Not Available |
| 4 | EMP003 | Bob | Johnson |
Explanation:
- We have a table with employee data, including Employee ID, First Name, Last Name, and Middle Name.
- The “Full Name” column uses the ISTEXT function to check if the Middle Name is provided:
- If the Middle Name is provided, the IF function adds it to the full name with a space before and after.
- If the Middle Name is unavailable (blank cell), the formula only includes the First and Last names in the full terms.
🌼 Example 6: Text Concatenation with VLOOKUP and ISTEXT 🌼
Suppose we have two tables: one with Customer information (Customer ID and First Name), and another with additional notes for some customers. If available, we want to create a new table combining the customer’s first name and other messages.
Customer Info Table:
| A | B | |
|---|---|---|
| 1 | Customer ID | First Name |
| 2 | C001 | John |
| 3 | C002 | Jane |
| 4 | C003 | Bob |
Additional Notes Table:
| A | B | |
|---|---|---|
| 1 | Customer ID | Notes |
| 2 | C001 | Has VIP status |
| 3 | C004 | Not Available |
New Table:
| A | B | |
|---|---|---|
| 1 | Customer ID | Full Name |
| 2 | C001 | =VLOOKUP(A2, 'Customer Info'!$A$2:$B$4, 2, FALSE) & IF(ISTEXT(VLOOKUP(A2, 'Additional Notes'!$A$2:$B$3, 2, FALSE)), " - " & VLOOKUP(A2, 'Additional Notes'!$A$2:$B$3, 2, FALSE), "") |
| 3 | C002 | =VLOOKUP(A3, 'Customer Info'!$A$2:$B$4, 2, FALSE) & IF(ISTEXT(VLOOKUP(A3, 'Additional Notes'!$A$2:$B$3, 2, FALSE)), " - " & VLOOKUP(A3, 'Additional Notes'!$A$2:$B$3, 2, FALSE), "") |
| 4 | C003 | =VLOOKUP(A4, 'Customer Info'!$A$2:$B$4, 2, FALSE) & IF(ISTEXT(VLOOKUP(A4, 'Additional Notes'!$A$2:$B$3, 2, FALSE)), " - " & VLOOKUP(A4, 'Additional Notes'!$A$2:$B$3, 2, FALSE), "") |
Explanation:
- We have two tables: “Customer Info” with customer information and “Additional Notes” with additional notes for some customers.
- The new table uses the VLOOKUP function to retrieve the customer’s first name from the “Customer Info” table.
- The ISTEXT function checks if additional notes are available for each customer from the “Additional Notes” table:
- If additional notes are available, the IF function adds them to the customer’s full name with a hyphen before the letters.
- If there are no additional notes (e.g., customer C003), the formula only includes the customer’s first name in the full name.
🌼 Example 7: Totaling Textual Data with SUM and ISTEXT 🌼
Suppose we have a table with sales data for different products and want to calculate the total sales revenue for products with a description.
| A | B | C | |
|---|---|---|---|
| 1 | Product ID | Product Name | Description |
| 2 | PROD001 | Pen | Blue ink pen |
| 3 | PROD002 | Notebook | 100 pages |
| 4 | PROD003 | Eraser | Not Available |
| 5 | PROD004 | Marker |
| 6 | Total Sales | =SUM(IF(ISTEXT(C2:C5), D2:D5, 0)) |
Explanation:
- We have a table with product sales data, including Product ID, Product Name, Description, and Sales Revenue.
- The “Total Sales” cell uses the ISTEXT function to check if the Description is provided for each product:
- If the Description is provided (textual), the IF function includes the Sales Revenue in the sum.
- If there is no Description (blank cell), the IF function includes 0 in the sum, effectively ignoring the product in the total sales calculation.
🌼 Example 8: Conditional Formatting with ISTEXT 🌼
Suppose we have a table with employee information, including Employee ID, First Name, Last Name, and Email Address. We want to use conditional formatting to highlight cells that contain email addresses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee ID | First Name | Last Name | Email Address |
| 2 | EMP001 | John | Doe | john@example.com |
| 3 | EMP002 | Jane | Smith | Not Available |
| 4 | EMP003 | Bob | Johnson | bob@email.com |
Explanation:
- We have a table with employee data, including Employee ID, First Name, Last Name, and Email Address.
- We want to highlight cells in the “Email Address” column using conditional formatting based on whether the cell contains a valid email address.
- Apply conditional formatting using the formula
=ISTEXT(D2)to the “Email Address” column to highlight cells that contain text (email addresses).
🌼 Example 9: Text Validation with ISTEXT and Data Validation 🌼
Suppose we have a table with a list of countries and their corresponding continent. We want to use data validation to ensure that the continent provided for each country is a valid text entry.
| A | B | |
|---|---|---|
| 1 | Country | Continent |
| 2 | USA | North America |
| 3 | Canada | Not Available |
| 4 | Brazil | South America |
| 5 | Australia | Oceania |
| 6 | Japan | Asia |
Explanation:
- We have a table with country data, including Country and Continent.
- We want to apply data validation to the “Continent” column to ensure that the values entered are valid text entries.
- Use data validation with the “Continent” column formula to allow only correct text entries.
🌼 Example 10: Textual Data Cleaning with ISTEXT and SUBSTITUTE 🌼
Suppose we have a table with a list of product codes, but some contain leading and trailing spaces. We want to clean up the data by removing these extra spaces.
| A | B | |
|---|---|---|
| 1 | Product ID | Cleaned ID |
| 2 | PROD001 | =IF(ISTEXT(A2), TRIM(A2), "Invalid") |
| 3 | PROD002 | =IF(ISTEXT(A3), TRIM(A3), "Invalid") |
| 4 | PROD003 | =IF(ISTEXT(A4), TRIM(A4), "Invalid") |
Explanation:
- We have a table with product codes in the “Product ID” column.
- The “Cleaned ID” column uses the ISTEXT function to check if the product codes are text.
- The IF function is used to remove leading and trailing spaces from the text using the TRIM function.
- If the product code is not text (e.g., empty cell), the formula returns “Invalid” in the “Cleaned ID” column.
🌼 Example 11: Textual Data Manipulation with ISTEXT and CONCATENATE 🌼
Suppose we have a table with employee information, including Employee ID, First Name, and Last Name. We want to create a username for each employee by combining their First Name and Last Name only if both values are textual.
| A | B | C | |
|---|---|---|---|
| 1 | Employee ID | First Name | Last Name |
| 2 | EMP001 | John | Doe |
| 3 | EMP002 | Jane | Not Available |
| 4 | EMP003 | Smith |
Explanation:
- We have a table with employee data, including Employee ID, First Name, and Last Name.
- The “Username” column uses the ISTEXT function to check if First Name and Last Name are text.
- The IF function, in combination with the AND function, concatenates the First Name and Last Name to form the username if both values are text.
- If either First Name or Last Name is not text (e.g., empty), the formula returns “Invalid” in the “Username” column.
🌼 Example 12: Textual Data Validation with ISTEXT and Named Ranges 🌼
Suppose we have a list of items, and we want to ensure that each item in the list has a unique name. We can validate data with the ISTEXT function and named ranges to check for duplicate titles.
| A | B | |
|---|---|---|
| 1 | Item ID | Item Name |
| 2 | ITM001 | Pen |
| 3 | ITM002 | Not Available |
| 4 | ITM003 | Notebook |
| 5 | ITM004 | Pen |
Explanation:
- We have a table with item data, including Item ID and Name.
- Select the range B2:B5 (Item Name column), and go to “Data” -> “Data Validation.”
- Choose “Custom” for the validation criteria.
- Enter the formula
=AND(ISTEXT(B2), COUNTIF($B$2:$B$5, B2)=1)in the formula box.- The ISTEXT function checks if the item name is text.
- The COUNTIF function contains if the item name appears only once in the range, ensuring it is unique.
- Set the error message for invalid data entries (e.g., duplicate names).
🌼 Part 3: Tips and Tricks 🌼
- Combine the ISTEXT function with other logical functions like IF, AND, OR, etc., to create more complex analytical expressions based on text validation or manipulation.
- Use the ISTEXT function to validate and clean textual data in your Excel sheets, ensuring you work with the correct data types.
- Be cautious when using the ISTEXT function with data imported from external sources, as formatting or data type discrepancies can affect the results.
- Always test your formulas with different scenarios and data types to ensure accurate and reliable results.
- When using data validation with ISTEXT, consider using named ranges for improved readability and maintainability of your Excel sheets.