Contextures

Formulas > Text > Combine Text

How to Combine Cells in Excel

Formula examples. Combine text in Excel, multiple cells, format numbers, dates. TEXT function, TEXTJOIN, Join operator &. Watch short videos, see written steps and screen shots, download free workbooks

combine text number format

Video: Excel Combine Text and Numbers

Combine Text from Multiple Cells

Add Spaces to Combined Text

Add Line Break to Combined Text

Combine Text and Number

Combine Text and Formatted Date

Formatting Examples

Custom Format Examples

Combine Text with TEXTJOIN

Get the Practice File

Video: Combine Text and Numbers

Watch 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

  • 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

The full transcript is below the video.

Video Transcript

If 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

   ✅ Click here for 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.

Combine Text from Multiple Cells

To combine text items from multiple cells into one cell,

  • In Excel 365, use the new TEXTJOIN function
    • This new function makes it easy to combine values from several cells.
    • See details and video in the TEXTJOIN function section below
    • There is also a new CONCAT function, to replace CONCATENATE, but it has limited options, and I don't use it
      • The older Excel CONCATENATE function is still available, for backward compatibility. It could possibly be removed in future versions of Excel.
  • In older versions of Excel, use the & (ampersand) operator
    • This is quicker and easier than the Excel CONCATENATE function

Use the & (Ampersand) Operator

In 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:

  1. Select the cell where you want to show the combined data
  2. Type an = (equal sign) to start the formula
  3. Click on the cell that contains the first text for the combined string
  4. Type the & operator (shift + 7)
  5. Click on the cell that contains the next text for the combined string
  6. Press Enter to complete the formula

If the text is in a formatted table, the formula will show structured references, with the column names.

combine text formatted table

If the text is not in a formatted table, the formula will show cell references.

combine text cell references

Add Spaces to Combined Text

When you combine cell values 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

  1. Select the cell in which you want the combined data
  2. Type an = (equal sign) to start the formula
  3. Click on the first cell
  4. Type the & operator
  5. Type the text string for the character that you want between the words, inside double quotation marks. For example:
    • Space: " " (double quote, space, double quote)
    • Comma Space: ", " (double quote, comma, space, double quote)
    • Semi-colon Space: ", " (double quote, semi-colon, space, double quote)
    • Line break: CHAR(10)
      NOTE: Turn on Wrap Text in the cell, if using a Line Break
  6. Type the & operator
  7. Click on the second cell
  8. Press Enter to complete the formula go to top

If the text is in a formatted table, the formula will show structured references, with the column names.

combine text space formatted table

If the text is not in a formatted table, the formula will show cell references.

combine text space cell references

Add Line Break to Combined Text

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

  1. Select the cell in which you want the combined data
  2. Type an = (equal sign) to start the formula
  3. Click on the first cell, to add its cell reference to the formula
  4. Type the & operator
  5. Type CHAR(10)
    • 10 is the number code for a line break
  6. Type the & operator
  7. Click on the second cell, to add its cell reference to the formula
  8. Press Enter to complete the formula
  9. Select the cell, and on the Home tab, in the Alignment group, click the Wrap Text command

combine text line break formatted table

Check the Formula References

If the text is in a formatted table, the formula will show structured references, with the column names.

combine text line break formatted table

If the text is not in a formatted table, the formula will show cell references.

combine text line break cell references

Combine Cells With Text and a Number

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

  1. Select the cell in which you want the combined data
  2. Type the formula, with text inside double quotes. For example: ="Due in " & A3 & " days"
    NOTE: To separate the text strings from the numbers, end or begin the text string with a space.
  3. Press Enter to complete the formula go to top

combine text line break cell references

Combine Text and Formatted Date

When 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):

  • ="Today is " & TODAY()

When you press Enter, the date appears as a long number, without any date formatting.

  • Today is 41905

combine text date

Add Date Formatting

To combine a text string with a formatted date, use the Excel TEXT function to format the date.

Follow these steps to create the formula:

  1. Select the cell where you want the combined data
  2. Type the formula, with text inside double quotes, and the date inside the TEXT function. For example, to show the full weekday name, for the current date:
    ="Today is " & TEXT(TODAY(),"dddd")
  3. Press Enter to complete the formula

How the TEXT Function works

In the Excel TEXT function: TEXT(TODAY(),"dddd")

  1. Date is the first argument,
  2. Formatting is the second argument.

In this example:

  • The TODAY function returns the current date.
  • The "dddd" formats the date as the full weekday name -- Tuesday

A few more date formats are in the screen shot shown below:

  • "mm-dd-yyyy"
  • "yyyymmdd"
  • "ddd mmm dd yyyy"

combine text date format

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 Numbers

Here are a few examples of using the TEXT function to format numbers.

NOTE: There are more examples of Custom formats on the Microsoft website go to top

combine text number format

  • Format a date as month/day/year:
    •   ="Payment is due " & TEXT(A2,"mm/dd/yyyy")
  • Format a number as currency:
    •   ="Amount due: " & TEXT(A3,"$#,##0.00")& " USD"
  • Format a number as a percentage:
    •    ="Your score is " & TEXT(A4,"0.00%")
  • Format a number as a fraction:
    •    ="Hours worked: " &TEXT(A5,"# ?/?")

Custom Number Format with Text

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

  1. In cell B4, type this formula, to show the current date: =TODAY()
  2. Right-click cell B4, and click Format Cells
  3. On the Number tab, click on the Date category
  4. Select the date formatting, that you'd like, such as 14-Mar-12
  5. Next, click the Custom category
  6. In the Type box, your selected date format will appear, such as:
    • [$-en-US]d-mmm-yy;@
  7. To add text before and/or after the date, type the text inside double quote mark. For example:
    • "Today is "[$-en-US]d-mmm-yy". Enjoy!";@
  8. Click OK, to apply the formatting

scroll through filter items

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.

date with custom number format that shows text

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.

The full video transcript is below the video, if you'd like to read it.

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

Video Transcript

If 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

   ✅ Click here for 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 Text

So I'm going to click here, where I'd like to see the full name and type an equal sign.

  • And then click on B2 which has the first name
  • To join the values, I'm going to type Shift 7, which creates an ampersand, and that joins text in Excel
  • Then I'll click on the last name.

Now, when I press Enter, it's combined the names, but they would look better if there were a space between them.

Add Space Character

So I'm going to click there, where the formula is, and just click after the B2.

  • So right after the first name, I'd like a space.
  • So I'll type another Shift 7, to get an ampersand.
  • And to create a space, I'm going to type a space here inside double quotes.
  • So I'll type, double quote, space, and a double quote

And now, when I press Enter, there's the first name, space , last name.

And I can just copy that down.

Combine Text and Numbers

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

  • I'll type equal, click on the product name, shift 7, and put a space in there.
  • Another ampersand, and click on the amount.
  • And that combines the product name and the amount, but this isn't formatted too nicely.

Format the Numbers

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

  • I can type equals TEXT, an open bracket.
  • And now it's looking for two arguments:
    • the value we want to format and
    • how we want it formatted
  • So the value is the amount, and a comma.
  • And I would like this as currency
    • So, inside quote marks, I will type quote
    • And then I'll use a dollar sign,
    • And a number sign, comma, number sign, number sign, 0.00
  • So that's going to format it with commas, and two decimal places
  • And I'll put double quotes at the end, and a closing bracket

When I press Enter, this is the formatting we would like to see inside our combined cell data.

Add Formatting to Formula

So instead of typing it again, in the other cell, I'm just going to copy this formula, except for the equal sign.

  • And go to this formula
  • And I'll select that E2, and just paste the text formula there
  • And when I press Enter, there's the formula now, with the number formatted

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 Function

In 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

  • 00:00 Introduction
  • 00:25 Old Way to Combine Text
  • 00:54 TEXTJOIN - List of Days
  • 02:20 TEXTJOIN With Formatting
  • 02:48 Named Ranges

Get the Sample Files

Combine 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

CHAR function

ADDRESS Function

TEXTSPLIT Function

INDEX / MATCH Functions

TEXTJOIN Function
     
  

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.

 

Combine Text and
Formatted Number Video

 

About Debra

Last updated: January 18, 2023 6:32 PM