Excel Formulas Examples | ExtendOffice


DATEEDATEYEARMONTHDAY
Count
Count cells equal to COUNTIFSUMPRODUCTEXACT
Count cells not equal to COUNTIF
Count cells equal to either x or y COUNTIFSUMPRODUCTSUM
Count cells equal to both x and y COUNTIFS
Count cells greater than or less than COUNTIF
Financial
Calculate interest payments per period or total IPMTCUMIPMT
Lookup
Lookup closest match value with multiple criteria IFMATCHINDEX
Lookup values from another worksheet or workbook VLOOKUP
Vlookup with dymanic sheet name VLOOKUPINDIRECT
Shipping cost calculator VLOOKUP
Dynamic worksheet or workbook reference INDIRECT
Math
Convert binary value to decimal or octal or hex value BIN2DECDECIMALBIN2OCTBIN2HEX
Convert hexadecimal value to decimal or binary or octal value HEX2DECDECIMALHEX2OCTHEX2BIN
Convert octal value to decimal or binary or hexadecimal value OCT2DECDECIMALOCT2HEXOCT2BIN
Convert decimal value to binary or octal or hexadecimal value DEC2DECDEC2OCTDEC2HEX
Convert decimal number to IP address MID
Convert decimal to whole number in Excel ROUND ROUNDDOWN ROUNDUP
Date and Time
Add business days to date WORKDAYWORKDAT.INTL
Add hours to time TIME MOD
Add minutes to time TIME MOD
Add hours minutes seconds to time TIME
Add months to date  
Add years to date DATEYEARMONTHDAY
Assign points based on late time IFVALUE
Add or subtract days to date
Calculate days hours minutes seconds between two dates TEXT
Calculate days remaining from today MAXTODAY
Calculate days remaining between dates
Calculate days remaining in month EMONTH
Calculate days remaining in year
Calculate difference between two dates DATEDIF
Calculate difference between two times IF
Calculate expiry date EMONTHEDATE
Calculate hours minutes seconds between to times HOUR  MINUTE SECOND
Calculate network time MOD
Calculate retirement date EDATE
Calculate overlapping days MAXMIN
Calculate overtime pay  
Calculate years months days between two dates DATEDIF
Convert date to Julian TEXT  YEAR  DATE
Convert date to month year day TEXT
Convert date to text TEXT
Convert datetime string to datetime LEFT  MID
Convert decimal hours to time  
Convert decimal minutes to time  
Convert time to another time zone MOD
Convert time to decimal hours  
Convert time to decimal minutes  
Convert time to decimal seconds  
Convert time to money  
Convert time to Unix DATE
Convert timestamp to time MID  TIME
Convert text to time DATEVALUE  VALUE  
Extract time only from datetime in Excel TIMEMOD
Extract date only from datetime in Excel INTTRUNC DATE
Get week number from date in Excel WEEKNUM
Get work hours between two dates in Excel NETWORKDAYS NETWORKDAYS.INTL
Get or calculate age from birth date in Excel YEARFRAC DATEDIF
Text
Abbreviate Words or Names TEXTJOIN ISNUMBER ROW INDIRECT LEN MATCH MID TRIM LEFT SUBSTITUTE FIND UPPER
Add area code or country code to phone number CONCATENATE
Add character before each word SUBSTITUTE
Add comma after first word REPLACE FIND
Add comma between names FINDREPLACE TRIM SUBSTITUTE
Add dashes to phone number REPLACE
Add dashes to SSN in a cell LEFT MID RIGHT SUBSTITUTE
Add leading zeros to fix text length TEXT
Add text in middle LEFT MID REPLACE
Add space after comma TRIM SUBSTITUTE
Add space between number and text TRIMREPLACE MIN FIND MAX IFERROR ROW INDIRECT LEN
Capitalize first letter of text string or each word UPPERLEN FIND LOWER REPLACE LEFT MID
Check if a cell contains a specific text ISNUMBER SEARCH FIND
Check if cell contains all of many things SUMPRODUCT ISNUMBER SEARCH COUNTA
Check if cell contains one of many things SUMPRODUCT ISNUMBER SEARCH
Check if a cell contains one of several values but exclude other values SUMPRODUCT ISNUMBER SEARCH
Check if cell contains some texts but not contains others COUNT SEARCH AND
Convert letter to number COLUMN INDIRECT
Check if cell contains number COUNT FIND
Combine cells with line break CONCATENATE
Combine date and time into one cell in Excel TEXT
Combine text and date into same cell in Excel TEXT CONCATENATE
Count comma separated values in a cell LEN SUBSTITUTE TRIM
Count specific character in cell LEN SUBSTITUTE
Count specific character in a range of cells SUMPRODUCT LEN SUBSTITUTE
Count specific words in cell LEN SUBSTITUTE
Count specific words in a range of cells SUMPRODUCT LEN SUBSTITUTE
Count number of characters in a cell LEN
Count number of characters in a range of cells SUMPRODUCT LEN
Count number of words in a cell LEN TRIM SUBSTITUTE
Count number of words in a range of cells SUMPRODUCT LEN TRIM SUBSTITUTE
Use double quotes in formulas CHAR
Check if cell equals any value in list SUMPRODUCT
Clean and reformat telephone numbers SUBSTITUTE
Combine cells with commas TRIM SUBSTITUTE CONCATENATE
Combine initial and last name LEFT CONCATENATE
Compare two or more text strings EXACT IF COUNTIF
Concatenate cells but ignore blanks TEXTJOIN
Convert numbers to text in Excel TEXT FIXED
Convert text to number in Excel VALUE RIGHT LEFT MID
Count keywords cell contains based on a list SUMPRODUCT ISNUMBER SEARCH
Count lines which are separated by line breaks in a cell or a range LEN SUBSTITUTE
Show specific text based on value in Excel REPT IF
Extract all words but first or last from a cell RIGHT LEN FIND LEFT SUBSTITUTE
Extract capital letters only SUBSTITUTE
Extract extension from filename RIGHT LEN FIND SEARCH REPLACE
Extract filename from a path in Excel MID FIND SUBSTITUTE LEN IFERROR
Extract first line of cell SEARCH LEFT
Extract first and last name from email FIND LEFT LEN RIGHT
Extract folder name from path FIND SUBSTITUTR LEN RIGHT
Extract from right until a character SEARCH RIGHT SUBSTITUTE LEN IFERROR
Extract initials from names MID IF FIND LEFT
Extract multiple lines from a cell TRIM MID REPT LEN SUBSTITUTE
Extract last line of text from a multi-line cell SUBSTITUTE REPT RIGHT TRIM
Extract nth word from text string in Excel SUBSTITUTE REPT MID TRIM LEN
Extract path from full path FIND SUBSTITUTR LEN LEFT
Extract substring from text string in Excel MID LEFT RIGHT SEARCH
Extract the last two words from a cell MID FIND SUBSTITUTE LEN
Extract text between parentheses from text string MID SEARCH
Extract word beginning with a specific character in Excel MID TRIM LEFT FIND REPT LEN SUBSTITUTE
Extract word which containing specific text in Excel MID TRIM FIND REPT MAX SUBSTITUTE
Extract first initial and last name from full name LEFT RIGHT FIND LEN
Extract first middle and last names from full name in Excel LEFT RIGHT FIND LEN MID SEARCH SUBSTITUTE
Extract text before or after second space or comma LEFT MID FIND SUBSTITUTE TRIM
Extract text after the last instance of a specific character RIGHT SEARCH LEN SUBSTITUTE TRIM REPT
Extract text between first and second comma from text strings MID SEARCH FIND SUBSTITUTE
Find and replace multiple values in Excel SUBSTITUTE INDEX
Find nth occurrence of character in a cell SUBSTITUTE FIND
Find most frequenst text in a range MODE INDEX MATCH
Find most frequenst text with criteria MODE INDEX IF MATCH
Find position of nth occurrence FIND SUBSTITUTE
Flip or reverse first and last names in Excel list MID SEARCH LEN
Get or extract the first word from text string in Excel FIND LEFT
Get or extract the last word from text string in Excel TRIM RIGHT REPT
If cell contains text then display in Excel IF ISNUMBER SEARCH
Make text same length REPT LEN
Make first letter lowercase LEFT LOWER REPLACE
Move or combine multiple cell contents into one cell CONCATENATE TRANSPOSE TEXTJOIN
Normalize text TRIM SUBSTITUTE LOWER
Remove extension from filename FIND LEFT
Remove first n characters LEN RIGHT
Remove first or last word from a string RIGHT LEN FIND LEFT TRIM SUBSTITUTE
Remove from right of text LEN LEFT
Excel remove last or trailing comma in a cell IF RIGHT LEFT LEN
Remove leading and tailing space CLEAN TRIM SUBSTITUTE
Remove line breaks from cells in Excel CLEAN TRIM SUBSTITUTE
Remove middle initial from full name in Excel LEFT RIGHT FIND TRIM SUBSTITUTE REPT
Remove prefix or suffix from string RIGHT LEN LEFT
Remove text from cell by matching the content SUBSTITUTE
Remove text from a cell based on specific position REPLACE SUBSTITUTE
Remove text based on variable position in Excel REPLACE FIND
Remove unwanted characters from cell in Excel SUBSTITUTE
Remove text before or after first or last specific character from text strings RIGHT LEFT FIND SEARCH LEN SUBSTITUTE
Remove text after or before the second or nth space from text strings RIGHT LEFT FINDLEN SUBSTITUTE
Remove text within parentheses or brackets from text strings FIND MID LEN SUBSTITUTE
Replace a specific character in a cell with another SUBSTITUTE
Reverse the text string in a cell in Excel TEXTJOIN MID
Split dimensions into individual length, height and width LEFT MID RIGHT FIND LEN SUBSTITUTE
Split dimensions into two parts in Excel LEFT RIGHT FIND LEN SUBSTITUTE
Separate numbers from units of measurement MAX ISNUMBER VALUE MID LEFT TRIM RIGHT
Separate octets of IP address in Excel LEFT MID LEN FIND
Separate Email addresses to usernames and domains LEFT RIGHT LEN FIND
Split dollars and cents FIND RIGHT LEN
Split a cell by first space in Excel LEFT FIND RIGHT LEN
Split number into individual digits MID COLUMN
Split sentence into words SEARCH MID ROW COLUMN IF
Split text and numbers MID   FIND  LEFT RIGHT LEN
Split text string at specific character in a cell in Excel MID   FIND  LEFT RIGHT LEN
Split text with delimiter in a cell in Excel MID   TRIM  SUBSTITUTE LEN
Strip or remove non-numeric characters from text strings TEXTJOIN IFERROR MID ROW INDIRECT
Strip or remove numeric characters from text strings TEXTJOIN IF ISERR MID ROW INDIRECT LEN
Strip or remove html tags from text strings MID   LEN
Trim text to n words SUBSTITUTE   FIND  LEFT