Excel Class NYC offers another free Excel class tutorial for our readers and subscribers. Everyone is encouraged to comment or reply about problems, questions, or suggestions. This tutorial can also be found at Excel Training.

In Advanced Excel Training you’ll learn advanced Excel functions like Vlookup, If/Then, IsError, and others. With so much important material to cover in Excel Class, there is rarely time to investigate Excel’s text functions. To learn more about Excel you are encouraged to register for Excel Class NYC.

Click here to download a practice workbook to follow along with the tutorial.

When working with data from the web or a database in Microsoft Excel, it is not uncommon that too much text appears in one cell and you need to extract only a section of that cell’s contents. Often the easiest solution is to use the Data > Text to Columns feature that separates your text at some delimiter like spaces or commas. (To learn more about databases you should investigate our Access Class website blog).

In part I of this three part series we examine four Excel text functions: Left, Len, Find, and Mid.

In the Text Functions workbook (that you downloaded from here), on the first worksheet (named Left), we’ll look at Excel’s Left function first. The purpose of the left function is to extract a known quantity of letters from the left side of a cell value. The syntax for the left function is:
=Left(Text, Number of Characters)
The Text must be a valid cell reference, named range, or the actual text string. A valid cell reference or named range always begins with a letter.
The number of characters is an integer expression that indicates how many symbols (letters, spaces, numbers, punctuation, etc.) that you want the Left function to display.

In cell B4, type =Left(
and now press the ctrl+a keys to show the insert function dialog as shown below

To extract the salutation (greeting) from cell A1, reference cell A1 for the Text input, and type 3 for the Num_chars input. Click the OK button to close the function arguments dialog. The result in cell B1 should read Mr.

In this example we’re not accounting for greetings like Mrs. or Miss that may require more than 3 characters. You can accomplish that result by nesting a len and find function in the left function (next topic).

Now let’s examine the Len function. In the Text Functions workbook navigate to the second worksheet (named Len) and select cell B4. The purpose of the len function is to tell you the number of characters in a cell (including spaces and punctuation). The syntax for the len function is:
=Len(Text)
The Text must be a valid cell reference, named range, or an actual text string.

In cell B4, type =Len(
and now press the ctrl+a keys to show the insert function dialog as shown below

By itself, it’s unlikely you’d ever need to use the Len function, however it becomes powerful when combined with the Find function.

Now let’s examine the Find function. In the Text Functions workbook navigate to the third worksheet (named Find) and select cell B4. The purpose of the Find function is locate a search term inside a cell value and return the position of the search term. The syntax for the Find function is:
=Find(FindText, Cell Reference, [starting position])
The FindText should be enclosed in quotation marks ” “. must be a valid cell reference, named range, or an actual text string.
The [starting position] is optional, it defaults to 1

In cell B4, type =Find(
and now press the ctrl+a keys to show the insert function dialog as shown below

The find text is “, ” (comma space); The within text is cell A4. The result is 14, the starting position of the state. Neat, but how can we now extract the state from the string. That’s where the Mid function comes in.

The last function to explore in this excel training blog is the Mid function. In the Text Functions workbook navigate to the fourth worksheet (named Mid) and select cell B4. The purpose of the Mid function is look inside the middle of a string (probably using find to specify a starting position) and to locate a known number of characters to the right of that (probably using a combination of the Len and Find function. The syntax for the Mid function is:
=Mid(Text, StartPostion, NumberOfCharacters)
The Text should be a valid cell reference, named range, or an actual text string.
The StartingPosition

In cell B4, type =Mid(
and now press the ctrl+a keys to show the insert function dialog as shown below

Our objective is to extract the two letter state code from cell A4. In this case, those two letters are always preceded by “, ” (comma space). In the Text box reference cell A4, that’s the search string. In the Start_Num box we’ll nest a find function to locate the “, ” characters and then fudge the position returned with a +2. In the Start_Num box enter
Find(“, “,a4)+2
The reason we add +2 is because we need to move 2 characters to the right of the “, ” to get to the first letter of the state code.
For the Number_Of_Characters we will hardcode the number 2, although a more elegant solution is to use the following functions:
LEN(A4)-FIND(“, “,A4)+2-(LEN(A4)-FIND(” “,A4,FIND(” “,A4)+2))
Which reads, from the length of A4, subtract the number of characters to the left of the state code, then subtract the number of characters from the second space to the end of the string.

To practice these functions on your own, Excel Training offers a free Excel text function test. You can download a practice file here.