AnswerBun.com

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

Web Applications Asked by Genny on January 13, 2021

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

3 Answers

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:

  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."

Correct answer by Erik Tyler on January 13, 2021

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}$")))))

enter image description here

Answered by marikamitsos on January 13, 2021

  1. First substitute the white space. Paste this to Cell B2: =SUBSTITUTE(A2, " ", "")
  2. 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)))
  3. 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.

Answered by Xylan Daniel on January 13, 2021

Add your own answers!

Related Questions

Delete share function in Google Sheet

1  Asked on November 14, 2021 by ingolf

 

Create a table of contents with hyperlinks in Google Docs

5  Asked on November 12, 2021 by codeobserver

 

Search for a comment on Facebook?

2  Asked on November 9, 2021

   

Why can’t I set my default font on google docs?

1  Asked on November 9, 2021 by princealbert

 

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir