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