Formulas > Text > Combine

# How to Combine Cells in Excel

How to combine text and number cells in Excel. TEXT function formats dates and numbers. Add text in formulas. TEXTJOIN examples. Watch short videos, see written steps and screen shots, download free workbooks

## Combine Text and Numbers

Watch this short video to see how to combine cells in Excel, with text and number format in the result. Written steps are below the video.

#### Video Timeline

• 0:00 Introduction
• 0:13 Simple Formula
• 0:52 Add Formatting
• 1:41 Another Date Format
• 2:04 Add the Year
• 2:29 More Formats
• 3:04 Get the Workbook

For the full transcript, go to the Combine Text and Formatted Number Video page

## Combine Text from Multiple Cells

To combine text from multiple cells into one cell,

• In Excel 365, use the new TEXTJOIN function
• This new function makes it easy to combine several cells.
• See details and videos in the TEXTJOIN function section below
• In older versions of Excel, use the & (ampersand) operator
• This is quicker and easier than the CONCATENATE function

### Use the & (Ampersand) Operator

To combine text with the ampersand (&) operator, follow these steps:

1. Select the cell where you want to show the combined data
2. Type an = (equal sign) to start the formula
3. Click on the cell that contains the first text for the combined string
4. Type the & operator (shift + 7)
5. Click on the cell that contains the next text for the combined string
6. 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 cell values 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, to act as a separator between the other characters

1. Select the cell in which you want the combined data
2. Type an = (equal sign) to start the formula
3. Click on the first cell
4. Type the & operator
5. Type the text string for the character that you want between the words, inside double quotation marks. 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
6. Type the & operator
7. Click on the second cell
8. 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 create a line break in the formula, by using the Excel CHAR function. This is the same as typing Alt+Enter in a cell that contains text.

Follow these steps to add the CHAR function, with 10 as its argument, to create a line break.

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

1. Select the cell in which you want the combined data
2. Type an = (equal sign) to start the formula
3. Click on the first cell, to add its cell reference to the formula
4. Type the & operator
5. Type CHAR(10)
• 10 is the number code for a line break
6. Type the & operator
7. Click on the second cell, to add its cell reference to the formula
8. Press Enter to complete the formula
9. Select the cell, and on the Home tab, in the Alignment group, click the Wrap Text command

#### Check the Formula References

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 another cell. In this example, text will be added to the payment terms.

1. Select the cell in which you want the combined data
2. 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.
3. Press Enter to complete the formula

## Combine Text and 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

For example, this simple formula combines a text string ("Today is ") with the current date (using the TODAY function):

• ="Today is " & TODAY()

When you press Enter, the date appears as a long number, without any date formatting.

• Today is 41905

#### Add Date Formatting

To combine a text string with a formatted date, use the Excel TEXT function to format the date.

Follow these steps to create the formula:

1. Select the cell where you want the combined data
2. 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")
3. Press Enter to complete the formula

#### How the TEXT Function works

In the Excel TEXT function: TEXT(TODAY(),"dddd")

1. Date is the first argument,
2. Formatting is the second argument.

In this example:

• The TODAY function returns the current date.
• The "dddd" formats the date as the full weekday name -- Tuesday

A few more date formats are in the screen shot shown below:

• "mm-dd-yyyy"
• "yyyymmdd"
• "ddd mmm dd yyyy"

NOTE: For other languages, use the applicable date codes, such as "jjjj" for the weekday name in French. To see how to switch between languages, download the sample file from the Functions section on the Excel Sample Files page. Look for FN0046 - Change Language for TEXT Function.

## Combine Text and Formatted Numbers

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

NOTE: There are more examples of Custom formats on the Microsoft website

• 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,"# ?/?")

## Custom Number Format with Text

Instead of using a formula in a cell, another way to combine text with dates or numbers is to use a custom number format. With this method, the dates or numbers are not affected -- they can still be used in other calculations, as numeric values. Only the cell appearance is changed, not the cell value.

Follow these steps to apply a custom number format that combines text and a formatted date.

1. In cell B4, type this formula, to show the current date: =TODAY()
2. Right-click cell B4, and click Format Cells
3. On the Number tab, click on the Date category
4. Select the date formatting, that you'd like, such as 14-Mar-12
5. Next, click the Custom category
6. In the Type box, your selected date format will appear, such as:
• [\$-en-US]d-mmm-yy;@
7. To add text before and/or after the date, type the text inside double quote mark. For example:
• "Today is "[\$-en-US]d-mmm-yy". Enjoy!";@
8. Click OK, to apply the formatting

The TODAY function in cell B4 still shows the current date, but now it has text before and after it.

And, if you look in the formula bar when the cell is selected, only the TODAY formula shows - none of the text is visible.

## TEXTJOIN Function

In Excel for Office 365, or Excel 2019 and later versions, you can use the TEXTJOIN function to combine text from multiple ranges, quickly and easily.

This short video shows a couple of TEXTJOIN examples, and there are written steps, and more examples, below the video. Click here to download the sample file that was used in this video.

Video Timeline

• 00:00 Introduction
• 00:25 Old Way to Combine Text
• 00:54 TEXTJOIN - List of Days
• 02:20 TEXTJOIN With Formatting
• 02:48 Named Ranges

NOTES:

#### TEXTJOIN Function Examples

The sections below have 5 examples of using the TEXTJOIN function

-- Example 1 - Simple Join

-- Example 2 - Join with condition

-- Example 3 - Condition and formatting

-- Example 4 - Dynamic array functions

-- Example 5 - Items with Line Breaks

### TEXTJOIN Arguments

The TEXTJOIN function has 3 required arguments in its syntax:

1. delimiter - A text string to separate the joined text items. Put the delimiter in double quotes, or refer to a cell that contains the delimiter
2. ignore_empty - TRUE, to ignore blank cells, or FALSE, to include them
3. text1 - The text items to join. This can be a text string, or range of cells, or an array of text strings

Note: You can add more text strings, if needed

• text2, text3, etc.

### Example 1 - Simple Join

This example uses a simple TEXTJOIN formula to join all the text in a range of cells - A2:A8.

• delimiter (separator) is a comma and space -- the first argument is ", "
• blank cells will be ignored -- the second argument is TRUE
• items in cells A2:A8 will be combined -- third argument is A2:A8

This formula is in cell D3, and it returns all the days in the list.

=TEXTJOIN(", ",TRUE,A2:A8)

NOTE

If the formula had FALSE as the setting for ignore_empty, the blank cell (A5) would be included in the result:

• Sun, Mon, Tue, , Thu, Fri, Sat

### Example 2 - Join with Condition

This example uses IF with TEXTJOIN to join the text in a range of cells - A2:A8, if it meets a specific condition..

• The separator is a comma and space, and blank cells will be ignored.
• The IF function checks for an "x" in column B. If an "x" is not found, IF returns an empty string for that weekday, which is ignored by TEXTJOIN, when TRUE is the 2nd argument.

The following formula is in cell D3, and it returns all the days in the list, where there is an "x" in column B.

=TEXTJOIN(", ",TRUE,IF(B2:B8="x",A2:A8,""))

### TEXTJOIN With Conditions Video

In this video, Sarah shows the steps for creating the TEXTJOIN formula with conditions. This example is in the TEXTJOIN Examples workbook, which you can get in the Downloads section, below.

#### Video Timeline:

• 00:00 Introduction
• 00:17 TEXTJOIN Function
• 00:27 Simple TEXTJOIN Formula
• 00:58 Start the Formula
• 01:30 Check for an "X"
• 01:45 Add a Condition
• 01:55 What to Show
• 02:15 Formula Result
• 02:22 Get the Workbook

### Example 3 - Condition and Formatting

This example example is similar to Example 2, but there are dates in column A, instead of weekday names.

• In the formula, the TEXT function formats the dates as "ddd", to show short weekday names in the result.
• The delimiter is a line break, created by the CHAR function, with 10 as the code..

=TEXTJOIN(CHAR(10),TRUE, IF(B2:B8="x", TEXT(A2:A8,"ddd"),""))

### Example 4 - Dynamic array functions

In this example, TEXTJOIN is combined with a few of Excel's new dynamic array functions -- FILTER, SORT and UNIQUE. Dynamic arrays are available in Microsoft 365 plans, Excel for the web, and Excel mobile apps.

The Sales sheet has 2 years of sales data, formatted as an Excel table. The columns are named ranges - YrCol, RegCol, CatCol, NameCol and QtyCol.

On the Targets sheet, there is another table, with a sales target number for each year and category.

In column E, a TEXTJOIN formula creates a list of sales reps who met the sales target, for that row's year and category.

#### Sales Reps Who Met Target

The TEXTJOIN function combines the results from a few of Excel's new dynamic array functions -- FILTER, SORT and UNIQUE. To show how those functions work, there's a demo on the Report sheet.

There are drop down lists at the top of the sheet, where you can select a year and category. A SUMIFS formula returns the sales target amount for the selected year and category.

Cell B4 is named YrSel, cell C4 is named CatSel, and cell D4 is named TgtSel.

This dynamic array formula is in cell C7, and the results spill down to the cells below, if necessary.

=IFERROR(UNIQUE(SORT(FILTER(NameCol, (YrCol=YrSel) *(CatCol=CatSel) *(QtyCol>=TgtSel)))), "--")

In this formula, the FILTER function returns the rep names from the Name column in the Sales table, if all 3 criteria are met:

1. The year is equal to the selected year (YrSel)
2. The category is equal to the selected category (CatSel)
3. The quantity is greater than or equal to the target amount (TgtSel)

Then, the SORT function puts those names in alphabetical order, and the UNIQUE function removes any duplicate names.

Finally, the IFERROR returns two dashes ("--"), if there is an error in returning the sales rep names. For example, no sales reps reached the target amount for the selected year and category.

#### Show All Names in One Cell

On the Targets sheet, a similar formulas is used to create a list of sales reps. This is the function in cell E4 on the Targets sheet:

=IFERROR(TEXTJOIN(", ", TRUE, UNIQUE(SORT(FILTER(NameCol, (YrCol=B4) *(CatCol=C4) *(QtyCol>=D4))))), "--")

The TEXTJOIN function combines all the names, separated by a comma and space character, so the results are shown in a single cell, instead of spilling down a column.

### Example 5 - Items with Line Breaks

In this example, TEXTJOIN is combined with FILTER, and other functions, to create a list of order details, all in one cell, with line breaks.

The order details are pulled from a named Excel table, Sales_Data.

#### TEXTJOIN/FILTER Formula

On the OrderInfo sheet, there is a drop down in cell B3, where you can select an Order number.

Here is the formula in cell B4, which lists all the items from the selected order. The formula is colour coded to show the different functions, and there are details below on how the formula works.

• =SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

#### A) FILTER and CHOOSE

In the formula, FILTER returns records where the order number matches the order number in cell B3.

• FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)

With the CHOOSE function, 4 columns are selected for the FILTER

• For the first argument, there's an array of 4 numbers -{1,2,3,4}
• Next, these 4 columns are selected: Category, Product, Grams, Quantity
• Quantity is combined with CHAR(10) -- a line break

#### B) TEXTJOIN

Next, TEXTJOIN combines the FILTER results, with a comma and space character as the delimiter. Ignore blanks is set to FALSE

• TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3))

#### C) SUBSTITUTE

Finally, SUBSTITUTE cleans up the TEXTJOIN result. To remove extra delimiters, it replaces any "line break comma space", with a line break

• =SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

#### Completed TEXTJOIN/FILTER Formula

Here is the completed formula in cell B4

=SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

The completed formula returns what we need in cell B4:

• List of all items in the selected order
• Item details in this order - category, product, size and quantity
• Details separated by comma space
• Line break after each item's details

## Video: Combine Text and Numbers

This video shows other examples of how to combine cells in Excel. First and last names are combined, using the ampersand symbol, and a space character is placed between them. Then, text and numbers are combined, and the TEXT function formats the numbers.

The full video transcript is below the video, if you'd like to read it.

#### Video Timeline

• 00:00 Introduction
• 00:12 Formula to Combine Text
• 00:41 Add Space Character
• 01:16 Combine Text and Numbers
• 01:53 Format the Numbers
• 03:03 Add Formatting to Formula

### Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

In Excel you can use a formula to combine data from two or more cells.

So here we have a column with first name and last name, and we'd like to combine them.

#### Formula to Combine Text

So I'm going to click here, where I'd like to see the full name and type an equal sign.

• And then click on B2 which has the first name
• To join the values, I'm going to type Shift 7, which creates an ampersand, and that joins text in Excel
• Then I'll click on the last name.

Now, when I press Enter, it's combined the names, but they would look better if there were a space between them.

#### Add Space Character

So I'm going to click there, where the formula is, and just click after the B2.

• So right after the first name, I'd like a space.
• So I'll type another Shift 7, to get an ampersand.
• And to create a space, I'm going to type a space here inside double quotes.
• So I'll type, double quote, space, and a double quote

And now, when I press Enter, there's the first name, space , last name.

And I can just copy that down.

#### Combine Text and Numbers

You can also combine text and numbers.

So here we have a price list. Our orders have come in, and I'd like to combine the product name, and the total amount of each sale.

So I'll do the same thing that I did with the customers.

• I'll type equal, click on the product name, shift 7, and put a space in there.
• Another ampersand, and click on the amount.
• And that combines the product name and the amount, but this isn't formatted too nicely.

#### Format the Numbers

If I want to format numbers or dates when I combine them, then I can use the TEXT function. So just in a separate cell, we'll see how that works.

• I can type equals TEXT, an open bracket.
• And now it's looking for two arguments:
• the value we want to format and
• how we want it formatted
• So the value is the amount, and a comma.
• And I would like this as currency
• So, inside quote marks, I will type quote
• And then I'll use a dollar sign,
• And a number sign, comma, number sign, number sign, 0.00
• So that's going to format it with commas, and two decimal places
• And I'll put double quotes at the end, and a closing bracket

When I press Enter, this is the formatting we would like to see inside our combined cell data.

#### Add Formatting to Formula

So instead of typing it again, in the other cell, I'm just going to copy this formula, except for the equal sign.

• And go to this formula
• And I'll select that E2, and just paste the text formula there
• And when I press Enter, there's the formula now, with the number formatted

And when I copy it down, it formats all these amounts with currency

Then, text and numbers are combined, and the TEXT function formats the numbers.

## Get the Sample Files

Combine Basic: The workbook has the Combine text examples with the & operator, and number formatting. The zipped file is in xlsx format, and does not contain macros.

TEXTJOIN Get Started: Click here to download the sample file for the Get Started with TEXTJOIN video. For Excel 2019 or 365. The zipped file is in xlsx format, and does not contain macros.

TEXTJOIN Examples: This workbook has TEXTJOIN examples 1, 2 and 3, which work in Excel for Microsoft 365, and in Excel 2019 or later versions. The zipped file is in xlsx format, and does not contain macros.

TEXTJOIN Dynamic: This workbook has the TEXTJOIN with Dynamic Arrays examples, which work in Excel for Microsoft 365. The zipped file is in xlsx format, and does not contain macros.

TEXTJOIN Line Breaks: This workbook has the TEXTJOIN with Line Breaks example, which works in Excel for Microsoft 365. The zipped file is in xlsx format, and does not contain macros.

____________

## More Function Tutorials

CHAR function

How to Sum in Excel

Examples show how to sum in Microsoft Excel, with a simple SUM function, or formulas that sum based on one or more criteria. See how to fix formula problems, and for more power, use SUMIF, SUMFS, SUMPRODUCT, SUBTOTAL or AGGREGATE. Step-by-step videos, written instructions, sample files to download.

Split or Reverse Names

Quickly split first and last names into separate cells, with Excel tricks or formulas. See how to reverse order of names, from "Smith, Mary" to "Mary Smith".

Step-by-step videos, written instructions, sample files to download.

Last updated: November 8, 2022 11:53 AM