Combine Cells in Excel Without Concatenate

Combine Cells in Excel Without Concatenate

Good news, if you’re spelling challenged — or too lazy to type long words. You can combine cells in Excel, without CONCATENATE function. Keep reading, to learn the easy way to combine cells, and add some fancy formatting to the dates and numbers.

Use the & Operator

Yes, instead of using CONCATENATE, you can use the ampersand operator — & — to combine cell values in Excel. That’s a savings of 10 characters!

And in the hot weather we’re having this summer, it’s important to conserve your energy for more important things.

Even more good news — you don’t need to remember how to spell “ampersand”, and you can even use one of its less technical names — “Fancy And” or “Shift-7”.

Combine Two Cells

In the cell where you’d like to see the combined values from two other cells:

  • Type an = sign, to start the formula
  • Click on the first cell that you want to combine
  • Type an &
  • Click on the second cell that you want to combine.

In the screenshot below, the product name and amount are being combined, and the formula is:

=B2&E2

concatenate01

  • Press Enter, to complete the formula

The values from the two cells are combined into one continuous text string, showing the product name and price.

concatenate02

Add a Space Between Combined Text

Your formula to combine the product name and price cells worked as advertised, but the results would look better with a space between the product name and price. To create a space, you can include a text string in the formula.

  • Select the cell with the formula
  • Click after the first cell reference
  • Type the & operator
  • Type ” ” (double quote, space, double quote)
  • Type the & operator
  • Press Enter to complete the revision

The revised formula is:

=B2&” “&E2

The product name and price now have a space between them.

concatenate03

Format Numbers in Combined Cells

When you combine text with a date or number, you can format the result by using the TEXT function. The TEXT function has two arguments — the cell reference, and the formatting. In this example, you can format the number as currency, with two decimal places.

TEXT(E2,”$#,##0.00″)

  • Select the cell with the formula
  • Change the second cell reference, to include the TEXT function
  • Press Enter to complete the revision

The revised formula is:

=B2&” “&TEXT(E2,”$#,##0.00”)

The product name and price now have a space between them, and currency formatting on the number.

concatenate04

Help With Number Formats

If you need help with setting up the Number Format argument in the TEXT function, there are a few more examples on the Combine Cells in Excel page.

You can also format a sample cell in Excel, using the Number Format commands. Then, to see its formatting code:

  • Select the formatted cell
  • Press Ctrl+1, to open the Format Cells dialog box.
  • On the Numbers tab, click the Custom category
  • Copy the formatting from the Type box.
  • Close the dialog box, and paste the formatting into the TEXT formula

concatenate05

Video: Combine Cells in Excel Without Concatenate

To see the steps to combine cells in Excel without CONCATENATE, watch this short Excel video.

_____________