Part II: Working with Formulas and Functions
When a Number Isn’t Treated as a Number
If you import data into Excel, you may be aware of a common problem: sometimes the imported values
are treated as text.
Depending on your error-checking settings, Excel may display error indicators to identify numbers
stored as text. An error indicator appears as a green triangle in the upper-left corner of cells. In addi-
tion, an icon appears next to the cell. Activate the cell and click the icon, which expands to show a list
of options. To force the number to be treated as an actual number, select Convert to Number from
the list of options.
To control which error-checking rules are in effect, choose File ➪ Options and then select the Formulas
tab. You can enable any or all of the nine error types listed under Error Checking Rules.
Here’s another way to convert these non-numbers to actual values. Activate any empty cell and choose
Home ➪ Clipboard ➪ Copy (or press Ctrl+C). Then select the range that contains the values you need
to fix. Choose Home ➪ Clipboard ➪ Paste Special. In the Paste Special dialog box, select the Add
operation and then click OK. This procedure essentially adds zero to each cell—and, in the process,
forces Excel to treat the non-numbers as actual values.
Using Text Functions
Excel has an excellent assortment of worksheet functions that can handle text. You can
access these functions just where you’d expect: from the Text control in the Function
Library group of the Formulas tab.
Many of the text functions are not limited to text: They can also operate with cells that
contain numeric values. You’ll find that Excel is very accommodating when it comes to
treating numbers as text.
The examples discussed in this section demonstrate some common (and useful) things you
can do with text. You may need to adapt some of these examples for your own use.
This book’s website, http://www.wiley.com/go/excel2019bible, includes a copy of the sample
workbook for this chapter. The file is named Text Formulas.xlsx.
Joining text strings
One of the more basic text manipulation actions that you can do is joining text strings
together. In the example shown in Figure 11.1, we are creating a full name column by
joining together first and last names.
