Excel Cookbook

Chapter 5. Text Manipulation

Excel isn’t a word processor, but in many situations you’ll need to manipulate text strings and transform data to fit a particular model.

This chapter shows how to use formulas to perform common text manipulation exercises, including joining text, extracting numeric codes, removing extra spaces and characters, and applying text formats to currency, number, and date/time values.

Note

You can also perform many of the operations in this chapter using Excel’s Power Query tool. See Chapter 15 to find out more.

5.1 Concatenating Text

Problem

You have two or more text strings and want to join them.

Solution

Suppose cell A1 contains the text John, B1 contains Doe, and you want to combine the two text strings.

One method uses the & operator, which concatenates two text strings. Typing the formula =A1&B1 in cell C1, for example, adds the text in B1 to the end of that in A1 and returns the text JohnDoe. If you want to insert a space between the two text strings and return John Doe instead, you type the formula =A1&" "&B1; this adds a space to the end of the text in A1 before adding the text in B1.

Tip

You must surround any static text, such as spaces, with double quotes and omit them for anything you want Excel to evaluate, such as cell references. The formula =A1&B1, for example, joins the contents of cells A1 and B1, while the formula ="A1"&"B1" returns the text A1B1.

An alternative approach is to use the CONCAT function. Generally, you use =CONCAT(