Let's look at some Excel VLookup function examples and explore how to use the VLookup function as a worksheet function in Microsoft Excel:

The VLOOKUP function searching for a value in the first column of a table and retrieve the value in the same row in the index_num position. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. VLOOKUP function performs a vertical lookup. Lookup values must appear in the first column of the table, with lookup columns to the right.
The SUBSTITUTE function is a built-in function in Excel that is categorized as a Lookup & Reference Function.

It searches for the value you specify and returns a matching value from another column.

The matched value from a table.

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

lookup_value - The value to search for in the first column of a table.
table_array - The range of data from which to retrieve a value.
col_index_num - The column number in the table from which to retrieve a value. The first column is 1.
range_lookup - [optional] Enter FALSE to find an exact match. Enter TRUE to find an approximate match (default).

No special formatting is needed.

How to use VLOOKUP function in Microsoft Excel.

First Parameter
The first parameter is the value to search for in the table of data.

In this example, the first parameter is AD102. This is the value that the VLOOKUP will search for in the first column of the table of data.

Second Parameter
The second parameter is the table or the source of data where the vertical lookup should be performed.

In this example, the second parameter is B5:E9 which gives us to data to use in the vertical lookup.

Third Parameter
The third parameter is the position number in the table where the return data can be found.

In this example, the third parameter is 2. This means that the second column in the table is where we will find the value to return.

Fourth Parameter
The fourth parameter determines whether you are looking for an exact match or approximate match.

In this example, the fourth parameter is FALSE. A parameter of FALSE means VLOOKUP function is looking for an EXACT match for the value of AD102. A parameter of TRUE means that a close match will be returned.

Let's look at some Excel Substitute function examples and explore how to use the Substitute function as a worksheet function in Microsoft Excel:

The SUBSTITUTE function is as similar to the REPLACE function used in the excel. However, SUBSTITUTE function replaces one or more instances of a substituted text string with a specified character(s). Whereas REPLACE function only replaces the text in a specified position/location of a supplied string.
Substitute function can be useful for when you want to substitute old text with a new text in the given text. For example, if the given text is 9988-776-655 and you want it as 9988776655 then you can use SUBSTITUTE function to transform this.
The SUBSTITUTE function is a built-in function in Excel that is categorized as a Text Function.

Replace text based on content.

The string/text Value.

=SUBSTITUTE (text, old_text, new_text, [instance_num])

text - The original string to use to perform the substitution.
old_text - The text to replace.
new_text - The new text to replace with.
instance_num - The instance to replace. If omitted, every occurrence of the old text will be changed to the new text.

No special formatting is needed.

You can nest several SUBSTITUTE functions within a single formula to do several substitutions at a time, i.e. substitute multiple characters with a single formula.

Let's look at some Excel Replace function examples and explore how to use the Replace function as a worksheet function in Microsoft Excel:

The Excel REPLACE function replaces characters specified by position in a given text string with another text string.
The REPLACE function is a built-in function in Excel that is categorized as a Text Function.

Replaces a portion of text with a new piece of text.

The altered text.

=REPLACE (old_text, start_num, num_chars, new_text)

old_text - The original string value.
start_num - The starting position in the text to replace.
num_chars - The number of characters to replace in old_text.
new_text - The text to replace old_text with.

No special formatting is needed.

The REPLACE function is useful for replacing text at a known position in a given string. For example, the following formula replaces 4 characters starting at the 1st character:

Let's look at some Excel Search function examples and explore how to use the Search function as a worksheet function in Microsoft Excel:

The SEARCH function is a way to find a character or string within another cell, and it will return the value associated with the starting place. In other words, if you are trying to figure out where a character is within the cell that contains a word, sentence or other type of information, you could use the SEARCH function.
SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.
The SEARCH function is a built-in function in Excel that is categorized as a Text Function.

Get the location of text in a string.

A number representing the location of find_text.

=SEARCH(find_text, within_text, [start_num])

find_text - The text to find.
within_text - The text to search within.
start_num - Starting position in the text to search. Optional, defaults to 1.

No special formatting is needed, the result will be shown as a number.

Let's look at some Excel Find function examples and explore how to use the Find function as a worksheet function in Microsoft Excel:

The FIND function in Excel is used to return the position of a specific character or sub-string within a text string.
The FIND function is a built-in function in Excel that is categorized as a Text Function.

Get the location of text in a string.

A number representing the location of find_text.

=FIND(find_text, within_text, [start_num])

find_text - The text to find.
within_text - The text to search within.
start_num - The starting position in the text to search. Optional, defaults to 1.

No special formatting is needed, the result will be shown as a number.

Let's look at some Excel Text function examples and explore how to use the Text function as a worksheet function in Microsoft Excel:

Text function in excel is basically used to convert the numeric values into a text format that is specified. So, it takes a value and turns it into text based on the numeric format of your choice.
The Text function is a built-in function in Excel that is categorized as a Text Function.

This function converts a number to a piece of text. The formatting for the text needs to be specified in the function.

A number as text in the given format.

=TEXT(value, format_text)

value - The numerical value that we need to convert to text. format_text - The format we want to apply.

No special formatting is required.

Let's look at some Excel Rept function examples and explore how to use the Rept function as a worksheet function in Microsoft Excel:

The Microsoft Excel REPT function returns a repeated text value a specified number of times. For example, =REPT("ABC",3) returns "ABCABCABC".
In financial analysis, This can be useful if you want to fill a cell, or pad values to a certain length. We can also build histograms, a chart commonly used in financial modeling, using the function by translating values directly into a certain number of (repeated) characters.
The Rept function is a built-in function in Excel that is categorized as a Text Function.

Repeats a piece of text a specified number of times.

The repeated text.

=REPT (text, number_times)

text - The text value to repeat.
number_times - It is the positive number that specifies the number of times to repeat text.

No special formatting is needed.

The REPT function repeats characters a specified number of times. For example, to repeat "A" six times, you can use the following formula:

Inputs to REPT can be variable. In the example shown, REPT is configured to repeat the string in column B using the count in column C. The formula in D5 is:

Let's look at some Excel Exact function examples and explore how to use the Exact function as a worksheet function in Microsoft Excel:

The Excel EXACT function compares two text strings, The case of the characters is taken into account, only words which are slept the same and which have upper and lower case characters in the same position will be considered as equal. EXACT is case-sensitive.
In financial analysis, we often need to compare two values while analyzing data and the EXACT function helps us to do the comparison.
The Exact function is a built-in function in Excel that is categorized as a Text Function.

This function compares two items of text and determine whether they are exactly the same.

Returns TRUE if the two strings are the same.
Returns FALSE if the two strings are different.

=EXACT(text1, text2)

text1 - The first text string to compare.
text2 - The second text string to compare.

If the two items of text are exactly the same the result of TRUE will be shown.
If there is any difference in the two items of text the result of FALSE will be shown.

Below are two examples of the EXACT function used with hard coded strings. In the first example, the strings are identical, in the second example, the only difference is the capital "G":

You can also use a normal equals sign (=) in a formula, but the comparison is not case sensitive:

Let's look at some Excel Dollar function examples and explore how to use the Dollar function as a worksheet function in Microsoft Excel:

The Excel DOLLAR function converts a number to text using the Currency number format. The TEXT function can do the same thing, and is much more versatile.
The Dollar function is a built-in function in Excel that is categorized as a Text Function.

This function converts a number into a piece of text formatted as currency.

A number as text in currency format.

=DOLLAR(number, [decimals])

Number - The number to be convert into a text string.
[Decimals] - An optional argument that specifies the number of decimal places to be rounded off. If omitted, it takes default value as 2 and rounds off to 2 decimal places.

No special formatting is needed. The result will be shown as a text entry.

Let's look at some Excel Abs function examples and explore how to use the Abs function as a worksheet function in Microsoft Excel:

The Excel ABS function returns the absolute value of a number. Negative numbers are converted to positive numbers, and positive numbers are unaffected.
The Abs function is a built-in function in Excel that is categorized as a Math/Trig Function.

This function calculates the value of a number, irrespective of whether it is positive or negative.
Find the absolute value of a number.

A positive number.

ABS function has one syntax and hence they can be written in Two different ways:

=ABS(number) or
=ABS(cell reference)

Number - the numeric value for which we need to calculate the Absolute value.
Cell reference: Reference to cells containing number.

The result will be shown as a number, no special formatting is needed.

Let's look at some Excel Small function examples and explore how to use the Small function as a worksheet function in Microsoft Excel:

It's slightly more difficult to find the largest or smallest number in a column. =MAX() shows the largest value and =MIN shows the smallest value. But far more difficult is finding the second smallest value or fourth smallest value.

The SMALL function can be used to return the smallest, second smallest, third smallest and nth smallest values from a range.
For example, if all salespersons are given the same target, we can find out which one achieved the sales target in the shortest time for a given year.
The Small function is a built-in function in Excel that is categorized as a Statistical Function.

Get the nth smallest value.

The SMALL function returns a Smallest numeric value.

This function examines a list of values and picks the value at a user specified position in the list.

=SMALL(array,k)

array - A range from which you want to return the nth smallest value
k - The position from the smallest to return.

No special formatting is needed.

Let's look at some Excel Large function examples and explore how to use the Large function as a worksheet function in Microsoft Excel:

It's slightly more difficult to find the largest or smallest number in a column. =MAX() shows the largest value and =MIN shows the smallest value. But far more difficult is finding the second largest value and third largest value.

The LARGE function can be used to return the largest, second largest, third largest and kth largest values from a range.
The Large function is a built-in function in Excel that is categorized as a Statistical Function.

Get the nth largest value.

The LARGE function returns a largest numeric value.

This function examines a list of values and picks the value at a user specified position in the list.

=LARGE(array,k)

array - A range from which you want to return the nth largest value
k - The position from the largest to return.

No special formatting is needed.

Let's look at some Excel Concatenate function examples and explore how to use the Concatenate function as a worksheet function in Microsoft Excel:

In your Excel workbooks, the data is not always structured according to your needs. Often you may want to combine data from two or more columns into a single column.
The Excel CONCATENATE function allows you to join 2 or more strings together. The CONCAT function replaces CONCATENATE in newer versions (Excel 2019) of Excel.
The Concatenate function is a built-in function in Excel that is categorized as a Text Function.

Join text together

Text joined together.

This function joins separate pieces of text into one item.

CODE function has one syntax and hence they can be written in Two different ways:

=CONCATENATE (text1, text2,...) or
=CONCATENATE (cell reference1, cell reference2,...)

Text - The text value to join together.
Cell reference - Reference to cells containing value..

No special formatting is needed, the result will be shown as normal text.

Many users wonder which is a more efficient way to concatenate strings in Excel - CONCATENATE function or "&" operator.

The only essential difference between CONCATENATE and "&" operator is the 255 strings limit of the Excel CONCATENATE function and no such limitations when using the ampersand. Other than that, there is no difference between these two concatenation methods, nor is there any speed difference between the CONCATENATE and "&" formulas.

And since 255 is a really big number and in real-life tasks someone will hardly ever need to combine that many strings, the difference boils down to the comfort and ease of use. Some users find CONCATENATE formulas easier to read, I personally prefer using the "&" method. So, simply stick to the concatenation technique that you feel more comfortable with.

Let's look at some Excel Code function examples and explore how to use the Code function as a worksheet function in Microsoft Excel:

The CODE function is the inverse of the CHAR function. CODE Function in Excel is a function that returns the code number (ASCII code) specified by the character from the character set for your computer.
For example, CODE("A") returns the code 65. The CODE function can be used to figure out numeric codes supplied to the CHAR function.
The CODE Function is not that much popular among excel community but as an excel expert, you should be aware of this function.
The Code function is a built-in function in Excel that is categorized as a Text Function.

Get the code for a character.

A numeric code representing a character.

This function shows the ANSI value of a single character, or the first character in a piece of text.

CODE function has one syntax and hence they can be written in Two different ways:

=CODE("text") or
=CODE(cell reference)

Text - The text for which you want a numeric code.
Cell reference: Reference to cells containing text.

No special formatting is needed, the result will be shown as a number between 1 and 255.

ASCII stands for American Standard Code For Information Interchange, is a character encoding standard for the digital communications. For each character that can be typed, has a unique integer number associated with it, may it be a character set, digits, punctuation marks, special characters, or control characters. For example, the ASCII value for [space] is 032. ASCII values ranging from 097-122 are reserved for the alphabets a-z in lower case.

Let's look at some Excel Char function examples and explore how to use the Char function as a worksheet function in Microsoft Excel:

The CHAR function is the inverse of the CODE function. CHAR Function in Excel is a function that returns the character specified by the code number (ASCII code) from the character set for your computer.
CHAR can be used to specify characters that are hard to enter in a formula. For example, CHAR(10) returns a line break on Windows, and CHAR(13) returns a line break on the Mac.
The Char function is a built-in function in Excel that is categorized as a Text Function.

Get a character from a number

A single character specified by a number.

This function converts a normal number to the character it represent in the ANSI character set used by Windows.

CHAR function has one syntax and hence they can be written in Two different ways:

=CHAR(number) or
=CHAR(cell reference)

Number - A number between 1 and 255.
Cell reference: Reference to cells containing number.

The result will be a character with no special formatting.

ASCII stands for American Standard Code For Information Interchange, is a character encoding standard for the digital communications. For each character that can be typed, has a unique integer number associated with it, may it be a character set, digits, punctuation marks, special characters, or control characters. For example, the ASCII value for [space] is 032. ASCII values ranging from 097-122 are reserved for the alphabets a-z in lower case.

Let's look at some Excel Clean function examples and explore how to use the Clean function as a worksheet function in Microsoft Excel:

As financial analysts, we would often import data from various sources and the CLEAN function would help remove non-printable characters from THE supplied text string. It is also useful in removing line breaks.
The Clean function is a built-in function in Excel that is categorized as a Text Function.

Strip non-printable characters from text

CLEAN function returns a text value.

This function removes any non-printable characters from text.
These non-printable characters are often found in data which has been imported from other systems such as database imports from mainframes.

CLEAN function has one syntax and hence they can be written in Two different ways:

=CLEAN(text) or
=CLEAN(cell reference)

Text: The text from which to clean.
Cell reference: Reference to cells containing text.

No special formatting is needed.

Let's look at some Excel Trim function examples and explore how to use the Trim function as a worksheet function in Microsoft Excel:

The Excel TRIM function remove extra spaces from text, leading only a single space between words and no space characters at the start or end of the text.
In financial analysis, the TRIM function can be useful in removing irregular spacing from data imported from other applications.
The Trim function is a built-in function in Excel that is categorized as a Text Function.

Remove extra spaces from text.

Text with removed extra spaces.

This function removes unwanted spaces from a piece of text.
The spaces before and after the text will be removed completely.
Multiple spaces within the text will be trimmed to a single space.

TRIM function has one syntax and hence they can be written in Two different ways:

=TRIM(text) or
=TRIM(cell reference)

Text: The text from which to remove extra space.
Cell reference: Reference to cells containing text.

No special formatting is needed.

The TRIM function is designed to remove only the space character, represented by code value 32 in the 7-bit ASCII character set. In the Unicode character set, there is one more space character called the non-breaking space, which is commonly used on web pages as the html character. The non-breaking space has a decimal value of 160, and the TRIM function cannot remove it by itself.

Let's look at some Excel Len function examples and explore how to use the Len function as a worksheet function in Microsoft Excel:

LEN function can be useful if we wish to get the length of a given text string as the number of characters. LEN function will also count characters in numbers,special character, spaces, but number formatting is not included.
The Len function is a built-in function in Excel that is categorized as a Text Function.

Get the length of given text string.

A numeric value.

This function counts the number of characters, including spaces and numbers, in a piece of text.

Len function has one syntax and hence they can be written in Two different ways:

=LEN(text) or
=LEN(cell reference)

Text: It is the text for which to calculate the length.
Cell reference: Reference to cells containing text.

No special formatting is needed.

Let's look at some Excel Proper function examples and explore how to use the Proper function as a worksheet function in Microsoft Excel:

The Excel PROPER function Capitalizes the first letter in a text string and Converts all other letters to lowercase letters.
The PROPER function in excel will not affect numbers, special characters and punctuation in the given text or string. It will only convert the first character to upper case and all the other letters to lowercase.
The Proper function is a built-in function in Excel that is categorized as a Text Function.

Capitalize the first letter in each word.

Text in proper case.

This function converts the first letter of each word to uppercase, and all subsequent letters are converted to lower case.

Proper function has one syntax and hence they can be written in Two different ways:

=PROPER(text) or
=PROPER(cell reference)

Text: A Text which you want converts to proper case.
Cell reference: Reference to cells containing text.

No special formatting is needed.

Let's look at some Excel Upper function examples and explore how to use the Upper function as a worksheet function in Microsoft Excel:

The Excel UPPER function will return the uppercase version of the text string given. Numbers and punctuation are not affected when we use UPPER function.
UPPER can be useful in arranging data or creating data in a certain format. For example, we often import data from external sources. The UPPER function will help us convert text to upper case if required.
The Upper function is a built-in function in Excel that is categorized as a Text Function.

Convert text to upper case (capital letters).

Text in upper case

This function converts all characters in a piece of text to upper case.

Upper function has one syntax and hence they can be written in Two different ways:

=UPPER(text) or
=UPPER(cell reference)

Text: A Text which you want converts to upper case.
Cell reference: Reference to cells containing text.

No special formatting is needed.