Show
Formulas > Text > Combine 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 NumbersWatch 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
For the full transcript, go to the Combine Text and Formatted Number Video page Combine Text from Multiple CellsTo combine text from multiple cells into one cell,
Use the & (Ampersand) OperatorTo 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 Spaces to Combined TextWhen 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, to act as a separator between the other characters
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 Cells With Text and a NumberYou 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.
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. TEXTJOIN FunctionIn 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. NOTES:
TEXTJOIN Function ExamplesThe sections below have 5 examples of using the TEXTJOIN function -- TEXTJOIN Arguments -- 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 ArgumentsThe TEXTJOIN function has 3 required arguments in its syntax:
Note: You can add more text strings, if needed
Example 1 - Simple JoinThis example uses a simple TEXTJOIN formula to join all the text in a range of cells - 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:
Example 2 - Join with ConditionThis example uses IF with TEXTJOIN to join the text in a range of cells - A2:A8, if it meets a specific condition..
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 VideoIn 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:
Example 3 - Condition and FormattingThis example example is similar to Example 2, but there are dates in column A, instead of weekday names.
=TEXTJOIN(CHAR(10),TRUE, IF(B2:B8="x", TEXT(A2:A8,"ddd"),"")) Example 4 - Dynamic array functionsIn 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 TargetThe 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:
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 CellOn 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 BreaksIn 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 FormulaOn 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.
A) FILTER and CHOOSEIn the formula, FILTER returns records where the order number matches the order number in cell B3.
With the CHOOSE function, 4 columns are selected for the FILTER
B) TEXTJOINNext, TEXTJOIN combines the FILTER results, with a comma and space character as the delimiter. Ignore blanks is set to FALSE
C) SUBSTITUTEFinally, SUBSTITUTE cleans up the TEXTJOIN result. To remove extra delimiters, it replaces any "line break comma space", with a line break
Completed TEXTJOIN/FILTER FormulaHere 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:
Video: Combine Text and NumbersThis video shows other examples of how to combine cells in Excel. First and last names are combined, and a space character is placed between them. Then, text and numbers are combined, and the TEXT function formats the numbers. 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. 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. ____________
How do I add text to the beginning of multiple cells in Excel?How to add text to the beginning of cells. In the cell where you want to output the result, type the equals sign (=).. Type the desired text inside the quotation marks.. Type an ampersand symbol (&).. Select the cell to which the text shall be added, and press Enter.. How do I add text before every cell?Using CONCATENATE to Add Text to the Beginning of all Cells. Click on the first cell of the column where you want the converted names to appear (B2).. Type equal sign (=).. Enter the function CONCATENATE, followed by an opening bracket (.. Type the title “Prof. ” ... . Select the cell containing the first name (A2). How do I add text to multiple cells at once?In Microsoft Excel, you can enter the same data or text into multiple cells at once using the below simple steps. Highlight all the cells that you want to have the same text. Type the text you want. After typing the text, instead of pressing Enter , press Ctrl + Enter .
How do I add text before a value in Excel?Notes:. For adding text at the beginning of a cell value only, you can use formula: =CONCATENATE("text ",A2).. For adding text at the end of a cell value, you can use formula: =CONCATENATE(A2," text"). The formula ="text" &A1, =A1& "text" or ="text" &A1& "text" can also help you.. |