Excel Functions

Click on a link to see the associated Excel functions

• Intro
• Numeric
• String/Text
• Boolean
• Date and Time
• Table
• Level 1
• Level 1 and 2
• All

String/Text Functions

LEFT

• Category: string
• User level: 1
• Syntax: =LEFT(string, number of characters)
• Example: =LEFT(A1, 3)

This returns the given of characters from the string, starting from the left. For example, if cell A1 has "The quick brown fox" and the formula is =LEFT(A1,7), this returns "The qui" (the first 7 characters from the left end). Note that the space counts as a character.

LEN

• Category: string
• User level: 1
• Syntax: =LEN(string)
• Example: =LEN(A1)

This returns the number of characters in the string, including spaces. The total number of characters in a string is often referred to as the "string length" (and it's unrelated to the amount of space it actually takes up on the screen or printed on paper).

MID

• Category: string
• User level: 2
• Syntax: =MID(string, starting number, number of characters to return)
• Example: =MID(A1, 6, 7)

This returns the given of characters from the string, starting from the starting number (often referred to as the "offset"). For example, if cell A1 has "The quick brown fox" and the formula is =MID(A1, 6, 7), this returns "uick br" (the first 7 characters starting at the 6th character). Note that the space counts as a character. When you get a part of a string, it's called a "substring". See SUBSTITUTE for replacing substrings.

RIGHT

• Category: string
• User level: 1
• Syntax: =RIGHT(string,number of characters)
• Example: =RIGHT(A1,7)

This returns the given of characters from the string, starting from the right. For example, if cell A1 has "The quick brown fox" and the formula is =RIGHT(A1,7), this returns "The qui" (the first 7 characters from the left end). Note that the space counts as a character.

SUBSTITUTE

• Category: string
• User level: 2
• Syntax: =SUBSTITUTE(string, old substring, replacement substring)
• Example: =SUBSTITUTE(A1, "good", "excellent")

This removes part of the the string and replaces it with another string (other characters). It will replace all instances of the string. For example, if A1 contains "The quick brown brown brown fox" and the function is =SUBSTITUTE(C25,"brown","fancy"), it will return "The quick fancy fancy fancy fox
". See MID for getting a substring without replacing it.

TRIM

• Category: string
• User level: 1
• Syntax: =TRIM(string)
• Example: =TRIM(A1)

This removes leading and trailing whitespace from the string, meaning it removes any spaces at the beginning or the end of the text. Whitespace includes the ordinary space created by space bar to put between words, but there are other kinds of whitespace (paragraphs, non-break characters, etc.). Leading and trailing spaces are usually ugly, or annoying, or just take up space, so it's a common practice to clean up the whitespace. Note that this does not remove "extra" whitespace from the inside of the string; see SUBSTITUTE for that.