Tips for Excel, Word, PowerPoint and Other Applications

Text Functions In Excel

Why It Matters To You

There are a number of functions in Excel that allow you to manipulate text, some of which we've already discussed in previous tips. These are very useful when you need to extract pieces of information out of a block of text, get information about a cell's contents, or re-organize text-based information.

How To ...

I've already addressed some of the other more commonly used text functions in the following articles:

LEFT, RIGHT, & MID
PROPER, LOWER, UPPER, and SUBSTITUTE
CONCATENATE

Let's get started. Here's a quick run down of each function along with a screenshot of the Function Argument window to show you. The descriptions are mostly copied from the Function Argument dialog for each function, with a couple of other notes from me.

CHAR - Returns the character specified by the code number from the character set for your computer. Here's one list of codes - ISO 8859-1 character set. I've never used this before, but you could use it in dashboards for special characters.

CODE - Returns a numeric code for the first character in a text string, in the character set used by your computer.

DOLLAR - Converts a number to text, using currency format.

EXACT - Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case-sensitive. Sometimes two values will look the same, but for some reason, they don't work as expected in a VLOOKUP formula or something that requires exact matching. EXACT can help you test your matching values to see if the problem lies in your comparison or somewhere else in your formula.

FIND - Returns the starting position of the first occurance of one text string within another text string. FIND is case-sensitive. I've found this to be the most useful if you can figure out the logic behind why your search string exists in the place it does. What I mean by this is consider the following 2 numbers: 12-3456789 and 123-45-6789. The first is the format of a federal Tax ID, the second is a social security number, either of which can be used by a small business as a tax filing ID number. So, you could FIND for "-" and it should return a value of either 3 or 4. If the value is 3, then you know it's a Federal Tax ID. You can also use FIND to establish the starting point for a MID function. Play around with it, but do remember that finding the rationale behind the pattern will help you make the most use of this function.

LEN - Returns the number of characters in a text string. While it may not seem that interesting at first glance, there are a number of examples where the reason for different LEN values tells us something in and of itself. I'm working on a project right now where I have cost center codes from two companies that are intermixed in a single invoice. How do you separate the company-specific charges out? Luckily, one company uses 5-digit cost center codes and the other company uses 6-digit codes. In this case, a simple IF paired with a LEN function gives me exactly the information I need.

REPLACE - Replaces part of a text string with a different text string. This operates differently from SUBSTITUTE in that SUBSTITUTE replaces on text string with another text string, where as REPLACE replaces a range of characters with a text string. For example, you can use SUBSTITUTE to replace "Maple" with "mayple", wherever it is found. If "Maple" doesn't exist, then no substitution takes place. On the other hand, you can use REPLACE to remove the first 5 characters of a text string and replace it with "mayple". REPLACE doesn't care what exists in those first 5 characters.

REPT - Repeats text a given number of times. Sometimes I just use this for filler text.

SEARCH - Returns the number of the character at which the first instance of a specific character or text string. SEARCH reads from left to right and is NOT case-sensitive. Similar to FIND, but is not case sensitive.

T - Checks whether a value is text and returns text if it is, or returns double quotes (empty text) if it is not. This can be useful for cleaning out numerical values and replacing them with

TEXT - Converts a value to text in a specific number format. The number format must be encased in quotes. I've never used this.

TRIM - Removes all spaces from a text string except for a single spaces between words. This is very useful. Sometimes you'll get data sets with a bunch of extra spaces. This is always a pain in the ass to clean up, especially if there are actual words in the field. TRIM will replace all of those extra spaces. You can then copy and paste as values the trimmed data back into your data set.

VALUE - Converts a text string that represents a number to a number. This is another very useful function for cleaning and normalizing data. Often times, you'll get data that looks like values, but isn't and because of this won't work in formulas as you might expect. A simple VALUE function will convert these very quickly.

There is a small number of text functions that I have not addressed: BAHTTEXT, CLEAN,and FIXED.

Notes

Last updated	9/19/08
Application Version	Excel 2003
Author	Michael Kan
Pre-requisites	None
Related Tips	LEFT, RIGHT, & MID PROPER, LOWER, UPPER, and SUBSTITUTE CONCATENATE