Written by Debra Dalgleish from Contextures Blog
In all versions of Excel, you can use a simple formula, with the & operator, to combine values from different cells. If you want the numbers formatted a certain way, use the TEXT function to set that up.
Video: Combine Text and Numbers
First, this video shows a simple formula to combine text and numbers in Excel. Then, see how to use the Excel TEXT function, to format the numbers in the formula.
You can type the formatting information inside the TEXT function. Or, put the formatting string in a different cell, and refer to that cell in the TEXT function.
To follow along, get the sample file on my Contextures site – How to Combine Cells
TEXTJOIN – Excel 365
If you’re using Excel 365, there’s a new TEXTJOIN function. It makes it easy to combine values from multiple cells.
This short video shows a couple of TEXTJOIN examples. First, see a simple formula to combine weekday names. Next, use the TEXT function inside TEXTJOIN, create formatted dates. There are written steps, and more examples, on my Contextures website.
Simple Formula
Even if you don’t have the new TEXTJOIN function, it’s easy combine values from multiple cells, using a simple formula. Just use the & (ampersand) operator, to join values together.
In this example:
- There’s text in cell A2, with a space character at the end.
- There’s an unformatted number in cell B2.
This formula, in cell C2, combines the text and number:
=A2 & B2
Or, if the text does not have a space character at the end, add one in the formula
=A2 & " " & B2
Format Numbers with TEXT Function
To join text with formatted numbers, use the TEXT function in the formula.
In this example:
- There’s text in cell A2, with a space character at the end.
- There’s a formatted date in cell B2.
This formula, in cell C2, combines the text and date, with formatting:
=A2 & TEXT(B2,"d-mmm")
Number Format Information
Instead of typing the number format inside the TEXT function, you could type those formats in another column, or on a different worksheet.
Then, in the TEXT function, refer to the cell with the required format.
For example, I’ve made a formatting list with named cells. Now, I can use those names in the TEXT function.
- FmtDMY d-mmm-yyyy
- FmtCurr $#,##0
- FmtFrac # ?/?
In this example, the formula in C2 uses the d-mmm-yyyy format, from the cell named FmtDMY:
=A2 & TEXT(B2,FmtDMY)
The other named formats are used in cells C5 and C8.
Currency – cell C5
=A5 & TEXT(B5,FmtCurr)
Fraction – cell C8
=A8 & TEXT(B8,FmtFrac)
Get the Workbook
To see more examples of combining text and numbers, and to get the sample workbook, go to the Combine Cells page on my Contextures site.
The zipped file is in xlsx format, and does not contain any macros.
________________________________
Combine Text and Formatted Numbers in Excel
________________________________
Combine Text and Formatted Numbers in Excel is a post from Contextures Blog and is not allowed to be copied to other sites