Excel 2019 Bible

253

C H A P T E R

11

Using Formulas to Manipulate

Tex t

IN THIS CHAPTER

Seeing how Excel handles text entered into cells

Exploring Excel worksheet functions that handle text

Getting examples of advanced text formulas

O

ftentimes, the work you do with Excel not only involves calculating numbers but also

includes transforming and shaping data to fit your data models. Many of these activities

include manipulating text strings. This chapter will highlight some of the common text

transformation exercises an Excel analyst performs and in the process give you a sense of some of

the text-based functions that Excel has to offer.

Working with Text

When you enter data into a cell, Excel immediately goes to work and determines whether you’re

entering a formula, a number (including a date or time), or anything else. That “anything else” is

considered text.

You may hear the term string used instead of text. You can use these terms interchangeably. Sometimes they even

appear together, as in text string.

A single cell can hold up to 32,000 characters—more than the number of characters in this chapter.

But Excel is not a word processor, and there’s no reason why anyone would need to come even close

to that number of characters in a cell.

If you need to display lots of text in a worksheet, consider using a text box. Choose Insert ➪ Text

➪ Text Box, click the worksheet to create the text box, and then start typing. Working with large

amounts of text in a text box is easier than editing cells. In addition, you can easily move, resize,

or change the dimensions of a text box. However, if you need to work with the text using formulas

and functions, the text must reside in cells.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.