3
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}$")))))
2
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 ...
2
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)&" &...
Only top voted, non community-wiki answers of a minimum length are eligible
Related Tags
conditional-formatting × 372google-sheets × 366
formulas × 113
google-apps-script × 15
google-sheets-dates × 14
google-sheets-arrayformula × 11
formatting × 7
regex × 7
google-sheets-query × 6
date × 6
regexextract × 5
data-validation × 4
google-sheets-cell-format × 4
importrange × 3
vlookup × 3
filter × 3
google-sheets-custom-function × 3
google-apps × 2
google-forms × 2
google × 2
if-this-then-that × 2
google-sheets-data-validation × 2
highlight × 2
search × 1
google-sheets-charts × 1