Combine Text and Formatted Numbers in Excel

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

There is a full transcript of the video beneath the clip.

Video 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 Formula

On 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.

-Start with an equal sign, then click on the first cell.

-Type an ampersand (shift 7).

-Then click on the number, press Enter.

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 Formatting

We’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.

To format the number, I’m going to use the TEXT function.

It needs a value, which is our number here, then a comma.

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.

I would like to see the full weekday name, and to do that, I’m going to type dddd, another double quote, and close the bracket.

Press Enter, and it says, Today is Wednesday.

Another Date Format

Now in this cell, instead of seeing the weekday name, maybe I’d like to see this day-month format. So I’ll copy this cell down to give us a starting point.

And then in our formula, instead of dddd, I would like d-mmm, press Enter. And there’s the date formatted just the way we have it here

Add the Year

Now, I could copy this down to the next cell, or maybe I’d like to include the year in that one — d-mmm-yyyy

I don’t need it in the next cell. I could have it on another sheet. And I could use that in my text function instead of typing the format each time.

Click on that cell and press Enter.

More Formats

Now in this cell, I’d like currency. So in this cell I’ll type the currency format.

So the number signs mean there may or may not be a thousands or hundreds or tens, but I always want a first number and I want a comma separator.

If I copy this down, it should pick up that format.

And for the final one here, we’d like fractions. Use # ?/? and copy that down. And there’s our hours with fractions.

Get the Workbook

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.

End of Transcript

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

________________________________