Forum Discussion

Deb Thompson's avatar
Deb Thompson
Copper Contributor
Sep 09, 2018

IF Statement for Multiple conditions

I am working on a template that will take phone number data and reformat it based on the below conditions: 

Phone # should contain 10 digits

- If < than 9 flag with "invalid"

- If 9 insert a 0

- If 10 return the value

- If 11 and contains 61 remove 61 and insert 0

- If 11 and does not contain 61 flag with "invalid"

- If > than 11 flag with "invalid"

 

1. Is this something I can do in one formula?

 

Any assistance would be greatly appreciated :-)

Thanks D

Excel
Formulas and Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Sep 09, 2018

    Hallo Deb

     

    My interpretation.

    =SWITCH(LEN(A5),8,"invalid",9,0&A5,10,A5,11,IF(ISNUMBER(SEARCH(61,A5)),SUBSTITUTE(A5,61,0),"invalid"),"invalid")&""

     

Resources