Home > Formulas > Text > Combine

Combine Text and Formatted Number Video

Video shows how to combine text and formatted numbers in Excel. Formulas use TEXT function to format numbers as dates, currency, or other number formats. Additional videos show different ways to combine text or merge cells.

Video: Combine Text and Formatted Numbers

Watch this short video to see how to combine cells in Excel, with text and number format. The transcript is below the video, and you can download the Excel workbook below, to follow along with 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 written steps and screen shots, and more examples, go to the How to Combine Cells in Excel page.

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
• 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: $#,##0

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

--------

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.

Video: Merge Cells or Combine Text?

This short video shows the difference between merging cells, and combining cell values with a formula.

For written steps and the sample file, go to the Merge or Combine Cells page.

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.

For written steps and the sample file, go to the How to Combine Cells in Excel page.

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

Get the Workbook

The workbook has the Combine text and formatted number examples from the video, and other TEXT function examples. The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

Combine Cells in Excel

Merge or Combine Cells?

Sum Functions -- Sum Cells

ConCat User Defined Function