Excel -- Combining Data

You can combine data from multiple cells into one cell, either "As is", or with additional text and formatting. Also, you can combine text, and the result of a function.

Combine Text from Multiple Cells
Add Spaces to Combined Text
Combine Text and a Number
Combine Text and a Formatted Date
Formatting Examples

  


Combine Text from Multiple Cells

To combine text from multiple cells into one cell, use the & (ampersand) operator.

  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 (shift + 7)
  5. Click on the second cell
  6. Press Enter to complete the formula

 

 

 

 


Add Spaces to Combined Text

When you combined text using the above formula, there was no space between the first and last names. To insert a space, you can include a text string in the formula.

  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 " " (double quote, space, double quote)
  6. Type the & operator
  7. Click on the second cell
  8. Press Enter to complete the formula

 

 

 

 

 


Combine Text and a Number

You can combine a text string with a number from a 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 " & B3 & " 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

 

 

 

 


Combine Text and a 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.

  1. Select the cell in which you want the combined data
  2. Type the formula, with text inside double quotes, and the date inside the TEXT function. For example:
    ="Today is " & TEXT(TODAY(),"dddd")
  3. In the TEXT function, the date is the first argument, and the 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.
  4. Press Enter to complete the formula

 

 


    Date without formatting


      Date with formatting


Formatting Examples

Format a date as 12/31/04:
  ="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,"# ?/?")

 

 

For more information on formats, see the following MSKB article, or Excel's Help files:
  XL: Controlling and Understanding Settings in the Format Cells Dialog Box
    http://support.microsoft.com/default.aspx?id=264372

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 5, 2008 10:41 PM