As we learned in earlier recipes, the best method for manipulating data in Excel is to break information into the smallest pieces possible. However, when porting this data to other locations or merging into reports, data may be better displayed by combining fields together; last name, middle initial, and first name for example. While smaller pieces are more efficiently manipulated, this broken form may not be read, when printed or communicated in reports, as easily.
In this recipe, you will learn how to combine a data field with textual characters to prepare data for review and reporting.
Combining data is a simple task that involves choosing the information that you wish to combine and adding any extra characters as needed. We will prepare the data below to be added to an employee timesheet. The full name field will need to display the employee number, last name, first name, and middle initial in the following form:
34567 — Doe, John F.
1. Begin by selecting an empty cell where the combined data is to be displayed:
2. Within the formula bar, type the following formula:
3. After pressing Enter, Cell A2 now displays the combined data. Click once again in cell A2, place your mouse over the small square that is displayed in the lower right-hand corner of the highlighting, and drag your mouse down to the last row that contains data; in this case row 4. Then release the mouse.
The formula entered used an ampersand (&), this symbol instructs Excel to combine information sequentially rather than attempting to add it together. By placing the hyphen (-), space ( ), comma (,), and period (.) in between quotation marks ("), Excel recognizes that the information is to be displayed as text. This combined with the ampersand combines the data within the cell references and quotations to build the new text string.
Excel has a built-in function for stringing data together called CONCATENATE. This formula allows textual strings to be listed sequentially without the use of an ampersand; however, the ampersand formula allows for greater flexibility and the inclusion of formula results within the combined string, whereas CONCATENATE treats all information as text.