LEN and MID Functions

The LEN and MID functions in Excel 365 are used for different text manipulation tasks.

LEN FUNCTION

The LEN function in Excel is used to count the number of characters in a given text string, including letters, numbers, symbols, spaces, and punctuation marks.

Syntax

=LEN(text)

text: The text string or cell reference for which you want to count the characters.

Example

Assume cell A4 contains the text "Tutor Joe's". You can find the length of this text using the following formula:

=LEN(A4)

This formula would return 11 because there are 11 characters in the text "Tutor Joe's". Here's a breakdown of the count

5 letters ("T", "u", "t", "o", "r")
1 space
4 letters ("J", "o", "e", "s")
1 apostrophe (')

So, when you use the formula =LEN(A4) where cell A4 contains the text "Tutor Joe's", Excel counts all these characters and returns the value 11 because that's the total number of characters in the text string "Tutor Joe's," including the space and the apostrophe.

Formula

Output

MID FUNCTION

The MID function extracts a specific number of characters from within a text string, starting at a specified position.

Syntax

=MID(text, start_position, num_chars)

text: The text string or reference to a cell containing the text from which you want to extract characters.
start_position: The position in the text from which to start extracting characters.
num_chars: The number of characters to extract.

Example

=MID(A4, 3, 3)

The formula =MID(A4, 3, 3) in Excel extracts a specific number of characters from within a text string, starting at a specified position.

Let's break down the example provided, where the text in cell A4 is "Tutor Joes":

MID(A4, 3, 3) instructs Excel to start extracting characters from the text in cell A4, beginning at the 3rd position and extracting 3 characters following that position.
"Tutor Joes" contains 9 characters including the space between "Tutor" and "Joes".

Therefore, when you use the formula =MID(A4, 3, 3), Excel begins extraction at the 3rd position within the text in cell A4 and retrieves 3 characters from that point onward, resulting in "tor". This outcome includes the characters "t", "o", and "r" from the text string "Tutor Joes", starting from the 3rd position.