Find or conditional format a column of phone numbers NOT in desired format

Question

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

Erik Tyler · Accepted 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}sd{3}sd{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:

LEN(A1) means the cell must have something in it (i.e., it has LENgth).
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."

marikamitsos · Answer

You mentioned

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

Please use the following conditional-formatting rule
=IF(A1<>"",(IF(ISNUMBER(A1),TRUE,NOT(REGEXMATCH(A1,"^d{4} d{3} d{3}$")))))

Xylan Daniel · Answer

First substitute the white space. Paste this to Cell B2: =SUBSTITUTE(A2, " ", "")

After that make your number to exactly 10 digit. Paste this to Cell C2: 
=IF(LEN(B2)=9,"0"&B2, IF(LEN(B2)=8,"00"&B2, IF(LEN(B2)=10,B2)))
Lastly add the space back to the right place.Paste this to Cell D2: 
=LEFT(C2,4)&" "&MID(C2,4,3) &" " &right(C2,3)

you shall have your expected result already. just that ur sample there, having cell with 12 digit, I don't know what kind of expected result for that.

Find or conditional format a column of phone numbers NOT in desired format

3 Answers

Add your own answers!

Ask a Question