Excel Tutorial Series – Text Functions

This is part three of the Excel Tutorial Series. Read all tutorial posts by browsing the Tutorial category.

Text functions are used to manipulate data found inside cells in your Excel worksheet. We'll be exploring five different text functions in this post:

• LEFT
• RIGHT
• MID
• FIND
• LEN

Descriptions of each function were taken from: Excel Function Reference

LEFT Function

Usage:

LEFT(text,num_chars)

LEFT returns the first character or characters in a text string, based on the number of characters you specify.

Arguments:

• Text is the text string that contains the characters you want to extract.
• Num_chars specifies the number of characters you want LEFT to extract.

---

RIGHT Function

Usage:

RIGHT(text,num_chars)

RIGHT returns the last character or characters in a text string, based on the number of characters you specify.

Arguments:

• Text is the text string that contains the characters you want to extract.
• Num_chars specifies the number of characters you want RIGHT to extract.

---

MID Function

Usage:

MID(text,start_num,num_chars)

MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

Arguments:

• Text is the text string that contains the characters you want to extract.
• Start_num is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.
• Num_chars specifies the number of characters you want MID to return from text.

---

FIND Function

Usage:

FIND(find_text,within_text,start_num)

FIND locates one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

Arguments:

• find_text is the text you want to find.
• within_text is the text containing the text you want to find.
• start_num specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

---

LEN Function

Usage:

LEN(text)

LEN returns the number of characters in a text string.

Arguments:

• Text is the text whose length you want to find. Spaces count as characters.

---

After reading the above, I hope you can understand how simple, yet how powerful these functions are. It helps if you think of them as I/O functions: you put in a certain type of value, and get a certain type of value back. If I could peek inside Excel and see how the functions are compiled, I imagine it would look something like this (or whatever the equivalent is in C++):

Function Left(Text As String, Num_Chars As Long) As String
Function Right(Text As String, Num_Chars As Long) As String
Function Mid(Text As String, Start_Num As Long, Num_Chars As Long) As String
Function Find(Text_To_Find As String, Text As String, Start_Num As Long) As Long
Function Len(Text As String) As Long

So the first three return strings as their value, while Find and Len return numbers. Let's review some samples so we can see how this works.

Extract information from cells

In a cell with two words and exactly one space, the LEFT function can extract the first word with the following formula:

=LEFT(A1,FIND(" ",A1)-1)

When I'm constructing a formula such as this, it helps sometimes to work backwards. So we'll start with the answer for this particular problem, and generalize the solution to work with any two-word cell.

=LEFT(A1,4)

Recall that the second argument of the Left function requires a number representing the number of characters you want to extract. The Find function returns a number representing the position of the found text inside the specified string. What we need is a count of characters to extract, to use as the second argument of the Left function.

I see that there's a space right after the 'n' in 'John', and it's the fifth character. I need a way to tell Excel that I want to extract everything up to that character. I remember the Find function will return the position of the space within the string (fifth position):

=LEFT(A1,FIND(" ",A1))

But we don't want to extract the space, so we subtract one from the total to get four. The function then becomes

=LEFT(A1,FIND(" ",A1)-1)

When you evaluate it, you can watch the formula work itself out.

1. =LEFT(A1,FIND(" ",A1)-1)
2. =LEFT("John Smith",5-1)
3. =LEFT("John Smith",4)

Extracting the last name is a similar process. Here are three formulas that can do it.

1. =MID(A1,FIND(" ",A1)+1,LEN(A1))
2. =MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))
3. =RIGHT(A1,LEN(A1)-FIND(" ",A1))

The only difference between the first and second formulas are that the first one just blindly extracts using the length of the entire string, whereas the second one actually calculates the exact number of characters to extract.

Extracting parts of phone numbers is done in a similar fashion. If cell A1 has the phone number "(800) GET-HELP":

• To extract area code: =MID(A1,FIND("(",A1)+1,3)
• To extract exchange: =MID(A1,FIND(")",A1)+2,3)
• To extract last four: =RIGHT(A1,4)

The last one is obvious, so I'll walk through the first two so you can see how they evaluate.

=MID(A1,FIND("(",A1)+1,3)
=MID("(800) GET-HELP",1+1,3)
=MID("(800) GET-HELP",2,3) (starting from the second character, extract the first three characters from the string "(800) GET-HELP")

=MID(A1,FIND(")",A1)+2,3)
=MID("(800) GET-HELP",5+2,3)
=MID("(800) GET-HELP",7,3) (starting from the seventh character, extract the first three characters from the string "(800) GET-HELP")

We can also format social security numbers the same way. Given a nine digit number in cell A1:

• To add hyphens: =LEFT(A1,3)&"-"&MID(A1,4,2)&"-"&RIGHT(A1,4)
• To remove hyphens: =LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4)

Use the first one to add hyphens to a nine digit number, the second to remove hyphens from an existing SSN. See Format SSN for more samples.

The beautiful thing about all of these formulas is that you can fill them across a row and/or down a column, to extract from multiple cells at the same time. So if you had a list of phone numbers in column A (assuming they all have the same format as above), you would put "=MID(A1,FIND("(",A1)+1,3)" in B1, "=MID(A1,FIND(")",A1)+2,3)" in C1, and "=RIGHT(A1,4)" and fill down as needed to separate each number into its components.

Further reading:

Excel Text Functions
Built-In Excel Functions
Excel Text Formulas