Chapter 25: Importing and Cleaning Data
2525
■ (^) The following formula adds .mp3 to the end of a cell:
=A2&".mp3"
■ (^) The following formula inserts a hyphen after the third character in a cell:
=LEFT(A2,3)&"-"&RIGHT(A2,LEN(A2)-3)
You can also use the Flash Fill feature to add text to cells.
Fixing trailing minus signs
Imported data sometimes displays negative values with a trailing minus sign. For example,
a negative value may appear as 3,498– rather than the more common –3,498. Excel does not
convert these values. In fact, it considers them to be nonnumeric text.
The solution is so simple it may even surprise you:
- Select the data that has the trailing minus signs. The selection can also include
positive values. - Choose Data ➪ Data Tools ➪ Text to Columns. The Text to Columns dialog box
appears. - Click Finish.
This procedure works because of a default setting in the Advanced Text Import Settings
dialog box (which you don’t even see normally). To display this dialog box, which is shown
in Figure 25.27, go to step 3 in the Text to Columns Wizard dialog box and click Advanced.FIGURE 25.27
The Trailing Minus for Negative Numbers option makes it easy to fix trailing minus signs in a
range of data.Following a data cleaning checklist
This section contains a list of items that could cause problems with data. Not all of these
are relevant to every set of data.■ (^) Does each column have a unique and descriptive header?
■ (^) Is each column of data formatted consistently?