Tips, Tricks, and Answers

The following articles are available for the 'Text Functions' topic. Click the article''s title (shown in bold) to see the associated article.

   Capitalizing Just a Surname
Changing the capitalization of text is, believe it or not, a common task in Excel. Common or not, it can be frustrating to figure out how to change the capitalization of just part of the text. Here's an easy way to change the capitalization of a surname that is part of a longer name.

   Checking for Text
Need to figure out if a particular cell contains text? You can use the ISTEXT function to easily return this bit of trivia.

   Cleaning Text
You can use the CLEAN worksheet function to remove any non-printable characters from a cell. This can come in handy when you want to pare down what is stored in a worksheet.

   Concatenating Names with Delimiters
Need to come up with a formula for combining lots of text from various cells? Here's a full discussion on how you can do so and even include delimiters between cell values, as appropriate.

   Converting Codes to Characters
Character codes are the numeric values used, by a computer, to signify various alphanumeric characters. You can use the CHAR worksheet function to discover the character code of any character.

   Ignoring Case in a Comparison
Do you want Excel to take the case of your text into account when it does comparisons in a formula? The IF statement ignores case, but here's a technique to get it to pay attention.

   Making PROPER Skip Certain Words
The PROPER worksheet function is used to change the case of text so that only the first letter of each word is uppercase. However, some words should not have the initial letter uppercased. There are a couple of ways you can modify the work done by PROPER, as described in this tip.

   Modifying Proper Capitalization
The PROPER worksheet function is used to change the case of text so that the first letter of each word is capitalized. If there are some words you don't want to have an initial cap, then you'll want to use the ideas presented in this tip.

   Reordering Last Name and First Name
If you've got a list of names in a column, you may want to change the order of each name. For instance, the name have the last name first, and you want to reorder the names so that they are in proper order. Here's how to do it.

   Repeating Cell Contents
Want to repeat cell contents over and over again within a single cell? Excel provides two ways you can duplicate the content.

   Returning an ANSI Value
Need to know the character value of the first character in a string? It's easy to do, without using a macro, by using the CODE function, described in this tip.

   Returning the Left-most Characters
When working with text in a formula, you may need to extract the left-most characters from a string of text or from a cell. You can do this using the LEFT worksheet function, described in this tip.

   Specifying a Language for the TEXT Function
You may want to use Excel to display dates using a different language than your normal one. There are a couple of ways you can go about this, as described in this tip.

   Specifying Proper Case
If you need to change the case of letters in a cell, one of the functions you can use is the PROPER function. This tip describes how to use the function to alter the appearance of your text.

   Using the CONCATENATE Worksheet Function
The process of combining string (text) values to make a new string is called concatenation. Excel provides the CONCATENATE function to accomplish the task, but there is an even easier way to join strings together.

   Using the REPT Function
Excel includes a handy function that allows you to repeat characters or strings of characters. How you use the REPT function is presented in this tip.