101 Ready-to-Use Excel Formulas

Chapter 3: Manipulating Text with Formulas

Often, the work you do with Excel involves not only calculating numbers but also transforming and shaping data to fit your data models. Many of these activities include manipulating text strings. This chapter highlights some of the common text transformation exercises that an Excel analyst performs, and in the process gives you a sense of some of the text-based functions Excel has to offer.

You can download the files for all the formulas at www.wiley.com/go/101excelformula.

Formula 13: Joining Text Strings

One of the more basic text manipulation actions you can perform is joining text strings together. In the example shown in Figure 3-1, you create a full-name column by joining together first and last names.

Figure 3-1: Joining first and last names.

How it works

This example illustrates the use of the ampersand (&) operator. The ampersand operator tells Excel to concatenate values with one another. As you can see in Figure 3-1, you can join cell values with text of your own. In this example, you join the values in cells B3 and C3, separated by a space (created by entering a space in quotes).

Excel also provides a CONCATENATE function ...