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

0
202
How to Highlight Only Texts in Google Sheets

Let’s see How to Highlight Only Texts in Google Sheets. I am beginning 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 we can learn how to highlight only texts in Google Sheets either in Row wise, Column Wise or entire sheet. For this purpose I’m making use of the Regexmatch formula. You just need to apply the custom formula which I’m providing 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 our 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 colour, highlight the cell by filling colour etc. Once 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 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 happen? Below is the answer.

find invalid date entry in Google Sheets

Find Cells Containing Invalid Date Format in Google Sheets

Actually our tutorial is discussing how to highlight only texts in a Column or Row in Google Sheets. I did 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 text, not date. So in a date wise calculation, it may cause invalid result. So with this conditional formatting rule, I could able to find invalid date entry, i.e. date entered in text format and I can correct that.

LEAVE A REPLY

Please enter your comment!
Please enter your name here