Part IV: Managing and Analyzing Data
Often, you’ll need to insert spaces, or some other delimiter, between the cells—for example,
if the columns contain a title, first name, and last name. Concatenating using the previ-
ous formulas would produce something like Mr.ThomasJones. To add spaces (to produce Mr.
Thomas Jones), use the TEXTJOIN function:
=TEXTJOIN(" ",TRUE,A1:C1)
The first argument of TEXTJOIN is the delimiter that you want to insert between the cell
values. The second argument is TRUE to ignore empty cells. If you set the second argument
to FALSE and there are empty cells, you’ll end up with two delimiters right next to each
other.
Figure 25.19 shows three examples of TEXTJOIN. In the first example, there are no empty
cells, so the second argument doesn’t matter. In the second and third examples, the second
argument is set to FALSE and TRUE, respectively, and the delimiter is changed from a space
to a comma (so it’s easier to see the duplication). Where empty cells are not ignored, two
commas are shown together.
FIGURE 25.19
The TEXTJOIN function inserts delimiters between cell values.
You can also use the Flash Fill feature (discussed earlier in this chapter) to join columns
without using formulas. Just provide an example or two in an adjacent column and press
Ctrl+E. Excel will perform the concatenation for the other rows.
Rearranging columns
If you need to rearrange the columns in a worksheet, you could insert a blank column and
then drag another column into the new blank column. But the moved column leaves a gap,
which you need to delete.
Here’s an easier way:
- Click the column header of the column you want to move.
- Choose Home ➪ Clipboard ➪ Cut.
