Google Sheets – How to Find or Format Phone Numbers Not in Desired Format

conditional formattingformulasgoogle sheetsregex

I have a column of phone numbers in that are in different formats. I want them all to be in this format 0000 000 000.

What I do now is manually edit each wrong number into the correct format.

How can I use a filter or conditional formatting to find all numbers that are NOT in this format (#### ### ###).

Here is the sample sheet:
https://docs.google.com/spreadsheets/d/1rlATwRPTkofEg0rB5-GSwwBpQDa7ZOoeNvhs0xPOsKA/edit?usp=sharing

Best Answer

Addressing strictly what you've requested in your post, I added a sheet ("Erik Help") with a custom conditional formatting rule applied to Column A:

=AND(LEN(A1),ISERROR(REGEXEXTRACT(A1,"^\d{4}\s\d{3}\s\d{3}$")))

This will highlight any entries in Column A that are not in the format "#### ### ###".

You can view this rule by clicking any cell in Column A, then choosing from the menu Format > Conditional formatting and double clicking on the rule that appears.

Two conditions must me met for the rule to take effect:

  1. LEN(A1) means the cell must have something in it (i.e., it has LENgth).
  2. The contents of the cell produce an (IF)ERROR when trying to (REGEX)MATCH the regular expression.

The regular expression means "startofstring-digits(four)-space-digits(3)-space-digits(3)-endofstrong."

Related Topic