# ISTEXT Function in Excel

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:

syntax
`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.

ABC
1Product IDProduct NameDescription
2PROD001PenBlue ink pen
3PROD002Notebook100 pages
4PROD003EraserNot 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.

ABCD
1Customer IDFirst NameLast NameFull Name
2C001JohnDoe`=IF(AND(ISTEXT(B2), ISTEXT(C2)), B2 & " " & C2, "")`
3C002JaneNot Available`=IF(AND(ISTEXT(B3), ISTEXT(C3)), B3 & " " & C3, "")`
4C003Smith`=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.

AB
1Item IDItem Name
2ITM001Pen
3ITM002Not Available
4ITM003Notebook
5ITM004

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

Excel
```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.

ABCD
1Employee IDFirst NameLast NameMiddle Name
2EMP001JohnDoeMichael
3EMP002JaneSmithNot Available
4EMP003BobJohnson

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:

AB
1Customer IDFirst Name
2C001John
3C002Jane
4C003Bob

Additional Notes Table:

AB
1Customer IDNotes
2C001Has VIP status
3C004Not Available

New Table:

AB
1Customer IDFull Name
2C001`=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), "")`
3C002`=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), "")`
4C003`=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.

ABC
1Product IDProduct NameDescription
2PROD001PenBlue ink pen
3PROD002Notebook100 pages
4PROD003EraserNot Available
5PROD004Marker

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

ABCD
1Employee IDFirst NameLast NameEmail Address
2EMP001JohnDoe[email protected]
3EMP002JaneSmithNot Available
4EMP003BobJohnson[email protected]

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.

AB
1CountryContinent
2USANorth America
3CanadaNot Available
4BrazilSouth America
5AustraliaOceania
6JapanAsia

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.

AB
1Product IDCleaned ID
2PROD001`=IF(ISTEXT(A2), TRIM(A2), "Invalid")`
3PROD002`=IF(ISTEXT(A3), TRIM(A3), "Invalid")`
4PROD003`=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.

ABC
1Employee IDFirst NameLast Name
2EMP001JohnDoe
3EMP002JaneNot Available
4EMP003Smith

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.

AB
1Item IDItem Name
2ITM001Pen
3ITM002Not Available
4ITM003Notebook
5ITM004Pen

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.