# Combining Data from Multiple Cells – Text and Numbers

This post will demonstrate a few techniques for combining text and numbers from separate cells into a single cell without losing the arithmetic functionality of the numbers.

We’ll pull this off with a neat trick that enables formulas to ignore the text portion of a cell.

Let’s not waste any time and jump right into the demo.

# Combining Text and Numbers from Separate Cells

We begin with a small table that contains names (*text*) in column A and amounts (*numbers*) in column B.

Our goal is to have the name and amount for each row of the table displayed in a single cell (*column C*).

This needs to be done dynamically so that a change in either the name or amount will trigger an update in the combined version in column C.

This can be accomplished with a **CONCATENATE** function.

**CONCATENATE** is a big, fancy word for “join things together”.

Excel has a dedicated **CONCATENATE** function, but most users will opt to use the simplified version of **CONCATENATE**… the ampersand (**&**) symbol.

For example, we begin in cell **C2** and write the following formula:

=A2 & B2

Pressing **ENTER** to commit the formula results in the following:

This is the purest form of concatenation; everything is just smashed together. It works, but it’s not visually appealing.

Concatenation is not limited to existing content. We can inject new content into the formula to enhance the results. In our case, we’ll inject a ‘space’ to provide visual padding between the name and the amount.

The ‘space’ needs to be surrounded by double-quotes. Any injected text needs to be surrounded by double-quotes.

=A2 & “ ” & B2

The updated formula’s result is a bit easier to read.

Think of the ampersand character as the word “and”. “Display the name in cell A2 **and** a space

**the number in cell B2.”**

__and__You can place anything you wish between the two double-quotes, like a dash, slash, or any other character.

Returning to using a ‘space’, we fill the formula down to the remaining rows to reveal the following:

# Formatting the Numbers in the Result

If we need to represent the numbers in the result as money (*we’ll use U.S. Dollars in our example*) we can’t just click the dollar sign button on the ribbon. The formatting codes for Currency Style are rendered inert with the presence of the concatenated text.

But not to worry; we have a solution.

We can apply number formatting to the result via the **TEXT** function.

The Excel **TEXT** function has the following syntax:

=TEXT(value, format_text)

**value**– This can be a static value, a reference to a cell that contains a value, or a formula that returns a value.**format_text**– This is the formatting code(s) that determine the way numbers are presented.

For our numbers, we want to display a dollar sign and a comma for every three place values. We also want to show zero if the number is zero.

Our codes for this are:

“$#,##0”

The formatting codes are always placed within a set of double-quotes.

Our formula would be updated as follows:

=A2 & " " & TEXT(B2, "$#,##0")

Committing our new formula to all rows of the tale gives us a much more appealing result.

As the values in column B change, the **TEXT** functions in column C will apply the appropriate number formatting.

For more information on the formatting codes and uses in the **TEXT** function, visit the following link.

### Microsoft Excel’s TEXT Function

## PRO TIP:

If you are unsure what formatting codes to use, an easy way to figure it out is to do the following:

- Select a cell with the number you want to format (
*ex: cell B2*) and press**F1**to open the number formatting dialog box. - Select the category (
*on the left*) then the desired formatting (*on the right*).

- Select the “
**Custom**” category (*on the left*) to reveal the underlying codes needed to support the selection from Step 2.

By applying different formatting options and examining the underlying codes, you will likely figure out many of the symbol’s purposes.

# Using Aggregation Functions Inside a Formatting Function

Because the first argument in the **TEXT** function can be a formula, we can embed an aggregation function, like **SUM**, into the formula to produce a formatted aggregation.

="Base: " & TEXT(SUM(B2:B5), "$#,##0")

The **SUM** function provides the aggregation which is then passed off to the **TEXT** function that applies the formatting codes.

The final touch is to concatenate the text string “**Base:** ” to the front of the formatted number.

We can write a similar formula for the bonus column.

="Bonus: " & TEXT(SUM(B2:B5), "$#,##0")

## “What if we want to add the ‘Base” and ‘Bonus’ results?”

If we write a **SUM** function that attempts to aggregate the results of the previous two formulas, we end up with a less than desirable result.

This occurs because we have embedded text in the same cell as the result of the **SUM** function.

# Separating the Formatting from the Aggregation

We can overcome the previous issue by concatenating the desired text in a **Custom Number Format** to produce the same result.

This allows you to place a simple aggregation function, like **SUM**, in a cell without any other modifiers.

We can then select the cell containing the above formula, press **F1** to open the **Format Cells** dialog box and apply the following custom number format.

“Base: ”$#,##0

We can apply a similar formatting code sequence to the bonus columns total in cell **C6**.

“Bonus: ”$#,##0

Adding the two **SUM** results now works perfectly.

*NOTE: I applied the following Custom Number Format to the result to spice it up like the other aggregations.*

“Total: ”$#,##0

## Practice Workbook

Feel free to Download the Workbook HERE.