Excel Formulas and Oracle Formulas

SSN - Remove dashes =CONCATENATE(LEFT(I2,3),MID(I2,5,2),RIGHT(I2,4))
SSN with dashes
=CONCATENATE(LEFT(B2,3),"-",MID(B2,4,2),"-",RIGHT(B2,4))
SSN - Add 0 to 7- and 8-digit SSNs
=IF(LEN(B2)=8,"0"&B2,IF(LEN(B2)=7,"00"&B2,B2))
First MI Last from 3 separate name fields
=TRIM(U2)&IF(V2="",""," "&V2)&" "&TRIM(W2)
Last Name from format "Smith, John"
=LEFT(N2,FIND(",",N2)-1)
Last Name from format "John Smith"
=RIGHT(J2,LEN(J2)-FIND(" ",J2))
First Name from format "John Smith"
=LEFT(L2,FIND(" ",L2)-1)
First Name from format "Smith, John"
=RIGHT(N2,LEN(N2)-FIND(",",N2)-1)
First Name without Middle Name (or Last Name)
=LEFT(H2,FIND(" ",H2))
Middle Name extracted from First Name
=RIGHT(N2,LEN(N2)-FIND(" ",N2))
Add 0 to zip code if 4 digits
=IF(LEN(T2)=4,"0"&T2,T2)
Add dash between zip + 4
=LEFT(N2,5)&"-"&RIGHT(N2, 4)
Two character MM
=IF(LEN(MONTH(K2)) = 1,CONCATENATE("0",MONTH(K2)),CONCATENATE("",MONTH(K2)))
Two character DD
=IF(LEN(DAY(K2)) = 1,CONCATENATE("0",DAY(K2)),CONCATENATE("",DAY(K2)))
MM DD YYYY
=CONCATENATE(C2,"/",D2,"/",YEAR(F2))
YYYY/MM/DD
=YEAR(N2)&"/"&IF(LEN(MONTH(N2))=1,CONCATENATE("0",MONTH(N2)),CONCATENATE("",MONTH(N2)))&"/"&IF(LEN(DAY(N2))=1,CONCATENATE("0",DAY(N2)),CONCATENATE("",DAY(N2)))
YYYYMMDD
=YEAR(N2)&IF(LEN(MONTH(N2))=1,CONCATENATE("0",MONTH(N2)),CONCATENATE("",MONTH(N2)))&IF(LEN(DAY(N2))=1,CONCATENATE("0",DAY(N2)),CONCATENATE("",DAY(N2)))
YYYYMMDD to MM/DD/YYYY
=MID(K2,5,2)&"/"&RIGHT(K2,2)&"/"&LEFT(K2,4)
MMDDYYYY to MM/DD/YYYY
=LEFT(N2,2)&"/"&MID(N2,3,2)&"/"&RIGHT(N2,4)
7 and 8 digit MMDDYYY
=IF(LEN(AX2)=7,0&LEFT(AX2,1)&"/"&MID(AX2,2,2)&"/"&RIGHT(AX2,4),LEFT(AX2,2)&"/"&MID(AX2,3,2)&"/"&RIGHT(AX2,4))
Fixed Length cell (ex/ 25, space filled)
=N2&REPT(" ",25-LEN(N2))
Date format (M/DD/YYYY) to Text format (MM/DD/YYYY)
=IF(LEN(MONTH(K2))=1,CONCATENATE("0",MONTH(K2)),CONCATENATE("",MONTH(K2)))&"/"&IF(LEN(DAY(K2))=1,CONCATENATE("0",DAY(K2)),CONCATENATE("",DAY(K2)))&"/"&YEAR(K2)
Date format (M/DD/YYYY) to Text format (MMDDYYYY)
=IF(LEN(MONTH(K2))=1,CONCATENATE("0",MONTH(K2)),CONCATENATE("",MONTH(K2)))&IF(LEN(DAY(K2))=1,CONCATENATE("0",DAY(K2)),CONCATENATE("",DAY(K2)))&YEAR(K2)
Text format (M/DD/YYYY) to Date format (MM/DD/YYYY)
=MONTH(L2)&"/"&DAY(L2)&"/"&YEAR(L2)
Phone #  (xxx) xxx-xxxx to xxxxxxxxxx
=MID(K2,2,3)&MID(K2,7,3)&RIGHT(K2,4)
Phone #  xxx-xxx-xxxx to xxxxxxxxxx
=MID(AN2,1,3)&MID(AN2,5,3)&RIGHT(AN2,4)
Phone #  (xxx) xxx-xxxx to xxx-xxx-xxxx
=MID(X2,2,3)&"-"&MID(X2,7,3)&"-"&RIGHT(X2,4)
Phone #  xxxxxxxxxx to xxx-xxx-xxxx
=LEFT(Q2,3)&"-"&MID(Q2,4,3)&"-"&RIGHT(Q2,4)
Age as of today
=INT((Today()-B9)/365.25)
Age of a specific date (ex/ 7/1/2011)
=IF(7>MONTH(D2),2011-YEAR(D2),IF(AND(7=MONTH(D2),1>=DAY(D2)),2011-YEAR(D2),(2011-YEAR(D2))-1))
Years of Service as of 1/1/2012
=IF(1>MONTH(M2),2012-YEAR(M2),IF(AND(1=MONTH(M2),1>=DAY(M2)),2012-YEAR(M2),(2012-YEAR(M2))-1))
If value appears 3 times
=IF(COUNTIF($A$2:$A$1675,A2)=3,A2)
Elig Date
=EOMONTH(E2+60,0)+1
Recent Hire date with duplicate records
=Max(If(H3=H2,G2+1))
<span><span><span><span><span>Password Generator
=CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))
</span></span></span></span></span></span></span></span></span></span></span></span></span>
</span></span></span>
</span>
</span>
</span>
<span><span><span><span><span>="Eo1!"&(IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=0,LEFT(A2,1),IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,LEFT(A2,1)&MID(A2,FIND(" ",A2)+1,1),LEFT(A2,1)&MID(A2,FIND(" ",A2)+1,1)&MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1))))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))
</span></span></span></span></span></span></span></span></span></span></span></span></span>
</span></span></span>
</span>
</span>
</span>
<span><span><span><span><span>Blank
=IF($A$3="","","Eo1!"&(IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=0,LEFT(A3,1),IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=1,LEFT(A3,1)&MID(A3,FIND(" ",A3)+1,1),LEFT(A3,1)&MID(A3,FIND(" ",A3)+1,1)&MID(A3,FIND(" ",A3,FIND(" ",A3)+1)+1,1))))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33)))
</span></span></span></span></span></span></span></span></span></span></span></span></span>
</span></span></span>
</span>
</span>
</span>
=ISNUMBER(SEARCH(Cell,"Text"))
This takes a two-letter state and converts it to "Pacific", "Mountain", "Central", "Alaska" or "Hawaii".
=if(ISNUMBER(SEARCH(A1,"WA,OR,CA,NV")),"Pacific",if(ISNUMBER(SEARCH(A1,"MT,ID,WY,UT,CO,AZ,NM")),"Mountain",if(ISNUMBER(SEARCH(A1,"ND,SD,NE,KS,OK,TX,MN,IA,MO,AR,LA,WI,IL,TN,MS,AL")),"Central",if(ISNUMBER(SEARCH(A1,"MI,IN,OH,PA,NY,VT,ME,NH,MA,RI,CT,KY,NJ,DE,MD,WV,VA,NC,SC,GA,FL,DC")),"Eastern",if(ISNUMBER(SEARCH(A1,"AK")),"Alaska",if(ISNUMBER(SEARCH(A1,"HI")),"Hawaii",""))))))
This takes a two-letter state and converts it to "PDT", "MDT", "CDT", "AKDT" or "HST".
=if(ISNUMBER(SEARCH(A1,"WA,OR,CA,NV")),"PDT",if(ISNUMBER(SEARCH(A1,"MT,ID,WY,UT,CO,AZ,NM")),"MDT",if(ISNUMBER(SEARCH(A1,"ND,SD,NE,KS,OK,TX,MN,IA,MO,AR,LA,WI,IL,TN,MS,AL")),"CDT",if(ISNUMBER(SEARCH(A1,"MI,IN,OH,PA,NY,VT,ME,NH,MA,RI,CT,KY,NJ,DE,MD,WV,VA,NC,SC,GA,FL,DC")),"EDT",if(ISNUMBER(SEARCH(A1,"AK")),"AKDT",if(ISNUMBER(SEARCH(A1,"HI")),"HST",""))))))



Age based on specific date

=DATEDIF(A2,B2,"y")


Oracle Formulas

#{securityContext.userInRole['ORA_ZBS_SALES_ADMINISTRATOR_JOB']==true || securityContext.userInRole['ORA_ZBS_SALES_VP_JOB']==true}
No results found