Search Contextures Sites

Related Tutorials

Sum Functions -- Sum Cells

VLOOKUP Function

INDEX / MATCH Functions

Count Functions -- Count Cells

INDIRECT Function

Combine Cells in ExcelYou can combine cells in Excel -- join data from multiple cells into one cell, either "As is", or with additional text and formatting. Also, you can combine text, and the result of a function.

Video: Excel Combine Text

Combine Text from Multiple Cells

Add Spaces to Combined Text

Add Line Break to Combined Text

Combine Cells With Text and a Number

Combine Cells With Text and a Formatted Date

TEXT Function Formatting Examples

Download the Sample File

More Function Tutorials

## Video: Excel Combine Text

Watch this short Excel video tutorial to see the steps for combining text and formatting the results.

## Combine Text from Multiple Cells

To combine text from multiple cells into one cell, use the

&(ampersand) operator.

- Select the cell in which you want the combined data
- Type an = (equal sign) to start the formula
- Click on the first cell
- Type the
&operator (shift + 7)- Click on the second cell
- Press Enter to complete the formula
If the text is in a formatted table, the formula will show structured references, with the column names.

If the text is not in a formatted table, the formula will show cell references.

## Add Spaces to Combined Text

When you combine cells with text using the above formula, there is no space between the first and last names. To insert a space, or another character, you can include a text string in the formula.

- Select the cell in which you want the combined data
- Type an = (equal sign) to start the formula
- Click on the first cell
- Type the
&operator- Type the text string for the character that you want between the words, for example:

- Space: " " (double quote, space, double quote)
- Comma Space: ", " (double quote, comma, space, double quote)
- Semi-colon Space: ", " (double quote, semi-colon, space, double quote)
- Line break: CHAR(10)

NOTE: Turn on Wrap Text in the cell, if using a Line Break- Type the
&operator- Click on the second cell
- Press Enter to complete the formula
If the text is in a formatted table, the formula will show structured references, with the column names.

If the text is not in a formatted table, the formula will show cell references.

## Add Line Break to Combined Text

Instead of showing characters between the words, you can use the CHAR function, with 10 as its argument, to create a line break. This is the same as typing Alt+Enter in a cell that contains text.

Be sure to format the cell with Wrap Text, to see the line break..

- Select the cell in which you want the combined data
- Type an = (equal sign) to start the formula
- Click on the first cell
- Type the
&operator- Type CHAR(10)
- Type the
&operator- Click on the second cell
- Press Enter to complete the formula
- Format the cell with Wrap Text
If the text is in a formatted table, the formula will show structured references, with the column names.

If the text is not in a formatted table, the formula will show cell references.

## Combine Cells With Text and a Number

You can combine cells to join a text string with a number from a cell.

In this example, text will be added to the payment terms.

- Select the cell in which you want the combined data
- Type the formula, with text inside double quotes. For example:
="Due in " & A3 & " days"

NOTE: To separate the text strings from the numbers, end or begin the text string with a space.- Press Enter to complete the formula
## Combine Cells With Text and a Formatted Date

When you combine text with a date, you can format it by using the TEXT function. Without formatting, the date will appear as a number, as in the screen shot below

- Select the cell in which you want the combined data
- Type the formula, with text inside double quotes, and the date inside the TEXT function. For example, to show the full weekday name, for the current date:

="Today is " & TEXT(TODAY(),"dddd")

- In the TEXT function, the date is the first argument, and the formatting is the second argument.
- The TODAY function returns the current date.
- The "dddd" formats the date as the full weekday name. You can use any other valid date format, such as:
- "mm-dd-yyyy" OR "yyyymmdd" OR "ddd mmm dd yyyy"

- Press Enter to complete the formula
## TEXT Function Formatting Examples

Here are a few examples of using the TEXT function to format numbers.

Format a date as month/day/year:

="Payment is due " & TEXT(A2,"mm/dd/yyyy")Format a number as currency:

="Amount due: " & TEXT(A3,"$#,##0.00")& " USD"Format a number as a percentage:

="Your score is " & TEXT(A4,"0.00%")Format a number as a fraction:

="Hours worked: " &TEXT(A5,"# ?/?")## Download the Sample File

To see the formulas, you can download the sample file. The zipped file is in xlsx format, and does not contain macros.

## More Function Tutorials

Sum Functions -- Sum Cells

VLOOKUP Function

INDEX / MATCH Functions

Count Functions -- Count Cells

INDIRECT Function

__

Contextures Inc., Copyright ©2015

All rights reserved.

Last updated: September 23, 2014 1:40 PM