#StandWithUkraine - Stop the Russian invasion

Join us and donate. We are contributing all book royalties from 2022 to present to:
Save Life in Ukraine and Ukraine Humanitarian Appeal.

Combine Data into One Column

Let’s perform the reverse action by combining data into one column with a spreadsheet formula, also called concatenation, using the ampersand symbol (&). Imagine you receive address data in four separate columns: street address, city, state, and zip code.

| Street        | City       | State  | Zip   |
| ------------- | ---------- | ------ | ----- |
| 300 Summit St | Hartford   | CT     | 06106 |

But imagine you need to geocode the addresses using a tool like the one we introduced in Chapter 2, which requires all of the data to be combined into one column like this:

| Location                          |
| --------------------------------- |
| 300 Summit St, Hartford, CT 06106 |

Using any spreadsheet, you can write a simple formula to combine (or concatenate) terms using the ampersand (&) symbol. Also, you can add separators into your formula, such as quoted space (" "), or spaces with commas (", "), or any combination of characters. Let’s try it with some sample data.

  1. Open the Combine Separate Columns sample data in Google Sheets, sign in with your account, and go to File > Make a Copy to create a version you can edit in your Google Drive. The sheet contains addresses that are separated into four columns: street, city, state, and zip.

  2. In column E, type a new header named location.

  3. In cell E2, type in =A2 & ", " & B2 & ", " & C2 & " " & D2. This formula combines the four items using ampersands, and separates them with quoted commas and spaces, as shown in Figure 4.7. Then press Enter.

  4. Click cell E2 and drag the bottom-right corner cross-hair downward to fill in the rest of the column.

Use ampersands to combine items, and insert quoted spaces with commas as separators.

Figure 4.7: Use ampersands to combine items, and insert quoted spaces with commas as separators.

Now that you have successfully combined the terms into one location column, you can use the Geocoding by SmartMonkey Google Sheets Add-on we described in Chapter 2 to find the latitude and longitude coordinates, in order to map your data as we’ll discuss in Chapter 7

For further reading, we recommend Lisa Charlotte Rost’s brilliant Datawrapper blog post about cleaning and preparing your spreadsheet data for analysis and visualization.21

Spreadsheets provide helpful ways to combine data columns. In the next section, we will introduce another spreadsheet function to convert numbers to text data.

  1. Lisa Charlotte Rost, “How to Prepare Your Data for Analysis and Charting in Excel & Google Sheets” (Datawrapper Blog, October 23, 2019), https://blog.datawrapper.de/prepare-and-clean-up-data-for-data-visualization/index.html.↩︎