Chapter 11. Manipulating Dates, Times, and Text

Most of the functions you’ve learned so far focus on crunching raw numbers. But Excel also provides functions that work with other types of data, including dates, times, and ordinary text. For text, for example, you may want Excel to pull first and last names from two different columns and join them in a single column. Or you may want to find and remove a word that appears in a bunch of column titles. Similarly, you may want to replace a character in a word, capitalize a name, or count the number of letters in a cell. Excel provides specialized text functions for all these tasks, and you’ll learn about them in this chapter.

Excel also gives you specialized functions for dates and times. These functions perform some indispensable tasks, like retrieving the current time and determining what day of the week a given date falls on. In addition, Excel lets you perform calculations with dates and times just as you would with ordinary numbers. This chapter introduces these techniques, too, and explains how Excel stores dates and times behind the scenes.

Manipulating Text

You can’t use arithmetic operators like + and – with text. If you try to, Excel displays a #VALUE error message. However, there’s one operator you can use: the concatenation operator (&), which joins together text. For example, imagine you have an individual’s first name in cell A1, and the last name in cell B1. You could join the values from these two cells to ...

Get Excel 2013: The Missing Manual now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.