Five Very Useful Functions For Working With Text

This page is dedicated to explaining how to use what I believe are the five most valuable formulas for working with text in Excel. They are useful on their own many times, but can become immensely powerful when nested (combined) with other formulas later as well. Mastering these five formulas will open up the door to many things that you may have never thought possible. All of these formulas can be used by putting actual text in the "text" area, but their true power is unlocked when using them on cell references as the data can then be dynamic.

For all of the following discussions, the syntax of the formulas will be displayed with all required portions in bold, and optional portions within square brackets and in regular type.

The "LEFT" Formula

Syntax: =LEFT(text,num_chars)

The left formula, put very simply, returns the left x characters of the cell. For example =LEFT("Hello",2) would return "He". As mentioned above, however, the best use comes from using it with a cell reference, as shown in the examples below.

Both the examples shown below use different data so that you can see the effects of changing the second argument (num_chars). The first example is asking for the left six characters, and the second example is asking for the left four characters:

The "RIGHT" Formula

Syntax: =RIGHT(text,num_chars)

The right formula is very similar to the left formula, but as you'd expect, it returns the rightx characters of the cell. Both the examples shown below, are asking for the right six characters from the cell:

The "MID" Formula

Syntax: =MID(text,start_num,num_chars)

Now the mid formula, designed to get text from the middle of a text string, is a little bit more complex, in that it takes another argument. In addition to knowing the text you want to evaluate, and the number of characters you want returned, it also needs to know which character you want to start at. To put the above syntax in plain English, you would build a MID formula as follows:

=MID(The text or cell you want to search, what character to start with, how many characters to return)

Now there are some things worth noting in the above examples. If you click on the results, you will see:

• The result in B3 (Visual) could also have been generated with the following formula: =Left(A3,6) This is just one area where there is more than one way to achieve the same results in Excel.
• The result in B4 (Basic) is interesting for two reasons. The first is that if you count the characters in Visual, you will see that there are only six letters, yet our formula starts the MID calculation at the seventh character. This is because spaces do count as a character. Had we used the formula =MID(A4,7,5), we would have returned " Basi". The second item of note is, as with the line above, we could have achieved this in a different way. =Right(A4,5) would have returned us the same result. (Basic)
• The last example is also interesting to know. If your final argument is greater than the amount of characters remaining after the start point, it gives you all of the remaining characters, as if it were the RIGHT formula.

The above examples were meant to display two things. The first was obviously how to use the MID formula. The second was that there are multiple ways to tackle the same problem in Excel. While some may be better than others, some are completely user preference, and what you become comfortable with.

The "LEN" Formula

Syntax: =LEN(text)

The LEN formula is used to return the length of the text string in question. While this may seem like a useless formula at first glance, I can assure you that it is anything but. When you start trying to manipulate text, I guarantee you that you will need to use this formula. One thing to remember about LEN is that it counts everything: spaces, punctuation, letters and numbers, it will count it all. Consider the following examples:

Now, if you've already started counting characters to verify what I calculated, you'll see that the space in "Visual Basic" was counted, as was the exclamation mark in "Hello!". But look at the last two examples of "Hello". Can you guess why row seven's answer is six characters and not five? It's because cell A7 does not contain "Hello" as A6 does.

A7 actually contains "Hello ". That last space can be very important, and can truly wreck havoc on formulas later if it isn't known about. This is just one of the many reasons we use the LEN formula. Another reason is that if you do actually need to count characters for any reason, it is fairly tedious work. LEN makes it very easy.

The "FIND" Formula

Syntax: =FIND(find_text,within_text,[start_num])

The FIND formula is used to return the position of a certain character within a text string. For example, in the word "Dog", o is the second letter. This can become very useful when you need to break apart text based on letters, say to split up words or product codes.

The find formula is even an additional level of complexity over the MID formula. While it takes the same number of arguments, the last one is actually optional, so you don't necessarily need to provide it. Again, we'll put the above syntax into plain English:

=FIND(The text or cell you want to find, what text you want to look in, what character to start with)

As mentioned, you have the option of leaving the last argument off, in which case Excel will just assume that you want to start looking at character number one. Some examples of the find formula are:

The first two examples, based on the word "Basketball", display the basic use of the FIND formula, but demonstrate the ability of ignoring the last number as an argument. My personal preference is to add the last number in most cases.

The second two examples, based on the number 12345, show that you don't actually need to enclose numbers in quotes. Since the FIND formula searches on a text argument, all arguments are converted to text before searching.

The third set of examples are based on the word "Doorknob", and display how to search for an entire string of text and a single letter. When searching for a string within a string (a word within a word), the position of the first character of the found string is returned. In the case of the single letter, we are looking for the position of the third "o", which occurs somewhere after the third character. We therefore start looking at the forth character.

The forth pair show examples of searching for a string of numbers within a text string. (Numbers formatted as per the example are actually text, not numbers. If they were number the resulting calculation would rest in the cell.) It also shows how to look for the second instance of a character within a cell. (Similar to the "o" example in the set above.)

The final pair shows that the FIND formula is Case Sensitive, and what happens if a character is not found. If you need a non-case sensitive version of this formula, you can use the =SEARCH formula, which functions in exactly the same way as the FIND formula for all other intents and purposes.

Try it yourself!

This downloadable workbook allows you to learn and play with text functions in a controlled environment, allowing you to see how these functions work. Each page is set up to challenge you to create strings of text data from given words. Download it and give it a try!