Formulas > Text > Combine Text How to Combine Cells in ExcelUse formulas to combine the contents of two or more cells, to display in another cell. For example, join first name from column A with last name from column B, and show the full name in column C. Examples show TEXT function, TEXTJOIN, and more ways to concatenate (join) text. Also see: How to Merge Cells |
Combine or Merge Cells?In Excel, there is an important difference between merging cells, and combining cell contents, in an Excel worksheet. The notes below, and the short video, show examples of the difference. Combine Cell ValuesThe examples on this page show how to combine the contents of two or more cells, to display in another cell, by using a formula. For example, join first name from column A with last name from column B, and show the full name in column C. Merge CellsThis page does NOT show how to merge two or more adjacent cells into one larger single cell. For example, you can merge cells A1 and B1, to create one large cell that spans two columns. Please visit the How to Merge Cells page for details, tips and examples on merging cells. |
Video: Merge Cells or Combine Text?This short video shows the difference between merging cells, and combining cell values with a formula. |
Video TranscriptIf 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 this video, you'll see how to combine text and numbers and then format those numbers if necessary, so they show us proper dates or currency or other formats. This is Debra Dalgleish from Contextures.com Simple FormulaOn this worksheet, we have a column with text and a column with numbers. In each text cell, I've ended with a space character. So when I combine the text and the number, there'll be a space character between them. I'm going to create a very simple formula.
And it nicely combined the text and the number. Now if I point to the fill handle, and double click to copy that down, all the others are combined, but there's no formatting. Add FormattingWe're going to start with an equal sign and click on the text cell, then our ampersand. And I want it to show the number, but formatted.
Then how do we want this formatted? We're going to put something inside double quotes. We're going to give it a text string to use, as the guide for formatting.
And it says, Today is Wednesday. Another Date FormatNow in this cell, instead of seeing the weekday name, maybe I'd like to see this day-month format.
And there's the date formatted just the way we have it here Add the YearNow, I could copy this down to the next cell, or maybe I'd like to include the year in that one -- d-mmm-yyyy
Click on that cell and press Enter. More FormatsNow in this cell, I'd like currency. So in this cell I'll type the currency format: $#,##0
If I copy this down, it should pick up that format. And for the final one here, we'd like fractions.
And there's our hours with fractions. -------- Thanks for watching this video. You can go to my website, contextures.com to get this workbook, and please subscribe to my Contextures YouTube channel, so you can see the latest videos as I post them. |
Combine Text from Multiple CellsTo combine text items from multiple cells into one cell,
Use the & (Ampersand) OperatorIn this example, there is a list of names, with Last name in column A and First name in column B. We'll create a formula in column C, to combine first and last name, and show each full name in a single cell. To combine text with the ampersand operator (&), follow these steps:
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 TextInstead 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..
Check the Formula ReferencesIf 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 Text and Formatted DateWhen 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):
When you press Enter, the date appears as a long number, without any date formatting.
Add Date FormattingTo combine a text string with a formatted date, use the Excel TEXT function to format the date. Follow these steps to create the formula:
How the TEXT Function worksIn the Excel TEXT function: TEXT(TODAY(),"dddd")
In this example:
A few more date formats are in the screen shot shown below:
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 NumbersHere are a few examples of using the TEXT function to format numbers. NOTE: There are more examples of Custom formats on the Microsoft website
|
Custom Number Format with TextInstead 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.
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.
|
Video: Combine Text and NumbersThis 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
Video TranscriptIf 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 TextSo I'm going to click here, where I'd like to see the full name and type an equal sign.
Now, when I press Enter, it's combined the names, but they would look better if there were a space between them. Add Space CharacterSo I'm going to click there, where the formula is, and just click after the B2.
And now, when I press Enter, there's the first name, space , last name. And I can just copy that down. Combine Text and NumbersYou 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.
Format the NumbersIf 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.
When I press Enter, this is the formatting we would like to see inside our combined cell data. Add Formatting to FormulaSo instead of typing it again, in the other cell, I'm just going to copy this formula, except for the equal sign.
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. |
TEXTJOIN FunctionIn Excel for Office 365, or Excel 2019 and later versions, you can use the new TEXTJOIN function to combine text from multiple ranges, quickly and easily. With TEXTJOIN, you can select a range of cells, instead of individual cells. Also, you can set a delimiter, as the character to go between the items, and specify whether to ignore blank cells, or include the empty cells. This short video shows a couple of TEXTJOIN formula examples, and there are written steps, and more examples, on the Excel TEXTJOIN function examples page. Video Timeline
|
Get the Sample FilesCombine 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. |
More Function Tutorials |
||
![]() |
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: August 9, 2023 12:14 PM