Text Functions

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.

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
Do you want to repeat the contents of a cell throughout the entire width of a cell? There are two ways you can do it in Excel—one method formulaic and the other through formatting.

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
Need only the left=most characters from a text string? You can use the LEFT worksheet function to easily get just what you need.

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
Want to join snippets of text together in a cell? You can do this using the CONCATENATE worksheet function, as described in this tip. You can also use the ampersand operator, which is a shorthand method of using the worksheet function.

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.