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
