Skip to main content
Topics#FormulasFrage von AINSLEY VIGOR
AINSLEY VIGOR (Ausure) fragte in #Formulas
18. Sept. 2022, 22:50

Referencing a second field in a formula

Hi, we have a formula set up that references the Phone field and strips digits as it is used in SMS.  For some reason it's not also using the MobilePhone field.  I want to include this field but keep getting syntax errors (not great with formulas).  I was trying to use OR and copy the same formula and change the Phone field to MobilePhone field but it's not working.

This is the current formula that I want to also use the MobilePhone field:

 

IF(LEFT(Phone , 3) = "+61",SUBSTITUTE(TRIM( "61" & TRIM(RIGHT(Phone, LEN(Phone)-3 )))," ", "" ),  IF(LEFT(Phone , 2) = "61",SUBSTITUTE(TRIM( "61" & TRIM(RIGHT(Phone, LEN(Phone)-2 )))," ", "" ),    IF(LEFT(Phone , 2) = "04", SUBSTITUTE(TRIM( "61" & TRIM(RIGHT(Phone, LEN(Phone)-1 )))," ", "" ),   IF(LEFT(Phone , 3) = "+64", SUBSTITUTE(TRIM( "64" & TRIM(RIGHT(Phone, LEN(Phone)-3 )))," ", "" ),     IF(LEFT(Phone , 2) = "02", SUBSTITUTE(TRIM( "64" & TRIM(RIGHT(Phone, LEN(Phone)-1 )))," ", "" ), SUBSTITUTE(TRIM( "" & TRIM(RIGHT(Phone, LEN(Phone)-2 )))," ", "" ) )))))

6 Antworten
  1. Keiji Otsubo
    18. Sept. 2022, 23:24

    Hi ,

     

    IF(ISBLANK( MobilePhone ),
    IF(LEFT(Phone , 3) = "+61",SUBSTITUTE(TRIM( "61" & TRIM(RIGHT(Phone, LEN(Phone)-3 )))," ", "" ),   
    IF(LEFT(Phone , 2) = "61",SUBSTITUTE(TRIM( "61" & TRIM(RIGHT(Phone, LEN(Phone)-2 )))," ", "" ),    
    IF(LEFT(Phone , 2) = "04", SUBSTITUTE(TRIM( "61" & TRIM(RIGHT(Phone, LEN(Phone)-1 )))," ", "" ),    
    IF(LEFT(Phone , 3) = "+64", SUBSTITUTE(TRIM( "64" & TRIM(RIGHT(Phone, LEN(Phone)-3 )))," ", "" ),      
    IF(LEFT(MobilePhone , 2) = "02", SUBSTITUTE(TRIM( "64" & TRIM(RIGHT(MobilePhone, LEN(MobilePhone)-1 )))," ", "" ),  
    SUBSTITUTE(TRIM( "" & TRIM(RIGHT(MobilePhone, LEN(MobilePhone)-2 )))," ", "" ) ))))),  
    IF(LEFT(MobilePhone , 3) = "+61",SUBSTITUTE(TRIM( "61" & TRIM(RIGHT(MobilePhone, LEN(MobilePhone)-3 )))," ", "" ),   
    IF(LEFT(MobilePhone , 2) = "61",SUBSTITUTE(TRIM( "61" & TRIM(RIGHT(Phone, LEN(MobilePhone)-2 )))," ", "" ),    
    IF(LEFT(MobilePhone , 2) = "04", SUBSTITUTE(TRIM( "61" & TRIM(RIGHT(MobilePhone, LEN(MobilePhone)-1 )))," ", "" ),    
    IF(LEFT(MobilePhone , 3) = "+64", SUBSTITUTE(TRIM( "64" & TRIM(RIGHT(MobilePhone, LEN(MobilePhone)-3 )))," ", "" ),      
    IF(LEFT(MobilePhone , 2) = "02", SUBSTITUTE(TRIM( "64" & TRIM(RIGHT(MobilePhone, LEN(MobilePhone)-1 )))," ", "" ),  
    SUBSTITUTE(TRIM( "" & TRIM(RIGHT(MobilePhone, LEN(MobilePhone)-2 )))," ", "" ) )))))
    )
Antwort schreiben...
0/9000