HomeGoogle DocsSpreadsheetHow to Highlight Only Texts in a Column or Row in Google...

How to Highlight Only Texts in a Column or Row in Google Sheets

Published on

Let’s see How to Highlight Only Texts in Google Sheets. Some of you may ask why should one highlight only texts or strings? There is a specific reason! Just read on to understand that.

I am going to post a few Google Sheets tutorials related to conditional formatting. Conditional formatting can play a vital role in data cleaning and with this tutorial, I’m trying to give you some idea in that direction.

In this conditional formatting tutorial, you can learn how to highlight only texts in Google Sheets either in Row-wise, Column-wise or in an entire sheet.

For this purpose I’m making use of the Regexmatch formula. You just need to apply the custom formula which I’m going to provide you here and that part I’ll explain below.

How to Highlight Only Texts in Google Sheets

The custom formula to highlight only texts in a column, row or an entire sheet is as below.

=ArrayFormula(REGEXMATCH(A1:A,"[a-zA-Z]"))

Here you just need to replace the range A1: A as per your requirement. In this Google Sheets tutorial, I’ve selected Column A and that’s why the range is so in the formula.

Now let us see how to apply this custom formula in Conditional Formatting.

Steps to Highlight Only Texts in a Column in Google Doc Spreadsheets:

1. Select the range, row or column first. In my example, I’m applying this conditional formatting rule to Column A.

2. Then go to the menu Format > Conditional formatting.

3. You can see the conditional formatting rules panel. Make sure that “Apply to range” is the correct range where you want the conditional formatting to apply.

4. From the drop-down under “Format cells if…” select “Custom formula is…” and paste our above Regex formula. Change the range in the formula if necessary.

5. Now under “Formatting style” you can see different formatting options. You can change the font color, highlight the cell by filling color etc. Once you complete the above steps, click “Done”

Highlight Only Text Conditional Formatting Rule

You can see the final result as below. Bizarre, right?

You can see that I’ve applied the above conditional formatting rule in a column that containing the date.

Again when you cross-check the column, you can further see that the conditional formatting rule highlighted certain cells containing dates. But our rule is to highlight Only Texts in a Column, right? Then why this has happened?

Below is the answer.

find invalid date entry in Google Sheets

How to Find Cells Containing Invalid Date Format in Google Sheets

Actually, my tutorial is discussing how to highlight only texts in a Column or Row in Google Sheets. I have done it correctly. But my purpose of the highlighting was different! I want to find cells containing invalid dates in a column.

In Column A, Cell A4 and A8 are containing texts, not dates. I mean they are dates formatted as text. So it would only behave like a text string.

That means, in a date wise calculation, that text values may cause an invalid result. So with this conditional formatting rule, I could able to find the invalid data entries, i.e. dates entered as text and I can correct that easily now.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here