Tips on using seven heavenly text functions

Harness the power of the suite of Text functions, such as RIGHT, LEFT, MID, FIND, LEN, TEXT, and REPLACE, and you'll be an unstoppable force. Okay, maybe that's a bit over the top. But learning to use these functions can really build your formula prowess. And it can be fun! Still a little extreme with the enthusiasm? Sorry, I'll tone it down a bit.

Want to pull out phone numbers from a bunch of cells, and you know the phone number is always at the end? Use the RIGHT function. Or is it always at the beginning? Use LEFT. Need to see if some text is buried somewhere in a cell? Use FIND. Want to do some fancy formatting of your own? Use the TEXT function.

Anyway, let's get started with some examples. All of these are shown in the embedded workbook, below, where you can really see them in action. 

 Read on to find out a little more about how I used the functions in the above examples.

RIGHT

In cell A2 I have a name and a phone number. I want to extract the phone number (555-0198). Because it has a 3 number prefix, a dash, and 4 more numbers, I know it's a total of 8 characters. I can use the RIGHT function, specifying cell A2 and the number of characters (8) from the right to get, like this:

=RIGHT(A2,8)

By the way, with these functions you don't necessarily have to refer to a cell. For example, =RIGHT("Hello, world!",6) returns the 6 rightmost characters, or "world!"

LEFT

In A4, I have a phone number with area code in parentheses, and the phone number is followed by the company name. I only want the phone number. With a quick glance and ten handy fingers (I still use them for counting, on rare occasion), I can see that the entire phone number will be 13 characters. I can use the LEFT function, specifying cell A4 and the number of characters (13) from the left to get, like this:

=LEFT(A4,13)

MID

Next, I have a customer alpha code (COWI) for Coho Winery in cell A6 that I'd like to extract. I know that in my spreadsheet I always have a four-digit number code followed by a comma, a space, the customer alpha code, and the full customer name. I can see that the alpha code will always start at position 7. I can extract the alpha code by using the MID function. MID works sort of like LEFT or RIGHT, but you give it one more piece of information - the starting point (here, it's the 7^h character, C). So, my formula looks like this:

=MID(A6,7,4)

FIND

In A8, I have a string of random numbers and letters in it, including my name somewhere in the middle. Just curious; where does my name start in the cell? I can use the FIND function, specifying the string to find in quotation marks ("Gary") and the cell in which to search. So, my formula looks like this:

=FIND("Gary",A8)

If it finds "Gary," it returns a number; in this case 12. That's where the "G" is. If it doesn't find "Gary," it returns the #VALUE! error. FIND is case-sensitive, by the way. If I look for "gary" or "GARY," I get the error. Like you, I'm sure, I'm not a big fan of errors.

LEN

Let's say I have a confirmation code, and I want to verify that it contains 14 characters, which is a test to see if it's valid. Here's a code in cell A10; I'll use the LEN function to get its length, like this:

=LEN(A10)

TEXT

Suppose I want to format a cash amount, but the format I want isn't available. For example, I can't express a number as British pounds (£) by using built-in formatting in the English U.S. version of Excel.  I can, however, use the TEXT function to convert this number to British pounds, like this:

=TEXT(A12,"£#,###,###.##")

In the formula, I enter the format I want to use in quotation marks. I inserted the £ symbol in the formula by holding down the Alt key and pressing 0163 on the numeric keypad (you need to use the numeric keypad, not the numbers in the row above the letters). Smashing!

After the £ symbol, I type #,###.## to tell Excel that it should use commas to separate groups (such as millions and thousands and hundreds) from each other with a comma, and to use a period for the decimal point. The downside? The result is text, so I can't do math on it. It's always something!

REPLACE

Another function I can use on text is REPLACE. Let's say I want to replace part of a string inside a cell with another string. For example, in A14, I want to replace " Gary" with "Frederique," since I'll be in Hawaii during an upcoming convention, and Frederique will be available to go to that convention in Hartford. Lucky Frederique! (I'm sure it's always pleasant in Hartford). I can replace "Gary" with "Frederique" by using REPLACE and FIND together, like this:

=REPLACE(A14,FIND("Gary",A14),4,"Frederique")

The first thing this formula does is use FIND to locate "Gary" in cell A14. "Gary" starts at position 22. Next, the REPLACE function looks in cell A14 for position 22 - which is the result of FIND("Gary",A14) - and replaces the 4 characters "Gary" with the string "Frederique." Neato mosquito!

Hope you learned some interesting stuff and you're now excited to find out even more about these and the several hundred other functions in Excel! Dive in!

 -- Gary Willoughby