Highlight Text Only in Google Sheets

Published on

We can use the ISTEXT function to highlight text entries in a range in Google Sheets. This helps identify data inconsistencies.

Highlighting text entries in a range can assist with data cleaning. Issues may arise when numbers or dates are mistakenly formatted as text, leading to invalid calculations. You can easily identify and resolve these issues by using conditional formatting to highlight text in a formula’s range.

Steps to Highlight Text Entries in a Range

  • Select the column, row, or 2D array where the highlighting applies. For this example, we’ll select the range A1:B6.
  • Click on Format > Conditional Formatting.
  • In the sidebar panel, choose Custom Formula is under Format Rules.
  • Enter =ISTEXT(A1) (use the cell reference of the top-left cell in your selected range). For example, if you’re highlighting the range B5:B100, use =ISTEXT(B5) in the formula field.
  • Choose a fill color under the Formatting style.
  • Click Done.
Conditional formatting rule to highlight text entries in a range

This will highlight only text entries in the selected range in Google Sheets.

Additional Tip: Highlighting Text Starting with Lowercase Letters

As part of data cleaning, you might want to highlight only text entries that start with lowercase letters.

The steps for highlighting are the same as in our previous example. The only change is in the custom formula. You can use this formula:

=AND(
   IFERROR(VALUE(A1), " ")=" ", 
   EXACT(LEFT(A1, 1), LOWER(LEFT(A1, 1)))
)
Example of highlighting text that starts with lowercase letters

Let me explain this rule:

  • IFERROR(VALUE(A1), " ") = " " – checks if the output is " ", identifying text (not numbers formatted as text).
    • IFERROR(VALUE(A1), " ") – returns " " if the cell contains text. The VALUE function produces an error when the cell contains text, and IFERROR converts that error into " ".
  • EXACT(LEFT(A1, 1), LOWER(LEFT(A1, 1)))EXACT tests if the first character in cell A1 matches its lowercase version. If TRUE, the text starts with a lowercase letter.

The AND function ensures that both conditions are met: the cell contains text (not a number formatted as text), and it starts with a lowercase letter.

This method highlights only text entries that start with a lowercase letter.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.