will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Combine Cells in Excel Without Concatenate
Good news, if you're spelling challenged -- or too lazy to type long words. You can combine cell values in Excel, without using the 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
- 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.
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.
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.
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
Watch the Combine Cells in Excel Video
To see the steps for creating a formula to combine and format cells, please watch this short Excel video tutorial.
_____________
July 30th, 2010 at 10:39 am
Debra- For many years I've wondered about the CONCATENATE and ampersand. I kept thinking I'd come across the answer by now, but since I haven't, I hope someone like you could provide it.
Why would I use CONCATENATE instead of &? The & is much easier and cleaner. The only time I can think to use the function is when I'm concatenating lots and lots of cells and a comma is easier to type than shift+7.
There are two ways in which I think the CONCATENATE function could provide value, but it does neither of these things.
1) If it allowed you to use a range of cells as the argument. For example, =CONCATENATE(A1:A4) would combine the values in those four cells.
2) If it allowed also let you input a delimited between values in the range. For example, =CONCATENATE(A1:A4, ", ") would combine those four cell, but put a comma between each entry. I know you could do this with a UDF, but I wish it were part of the function itself.
Sorry for the rant, but if you could enlighten me about why CONCATENATE exists and what I could be missing, I'd really appreciate it. It doesn't keep me up at night, but it causes me to become annoyed and confused more often than I'd like.
Thanks.
July 30th, 2010 at 1:32 pm
@David, you're not missing anything -- CONCATENATE could be a useful function, if it had the features that you suggested, but it doesn't.
I never use it, and if there's a long list of cells to join, I just copy and paste the ampersands and delimiters within the formula.
July 31st, 2010 at 12:53 pm
@David (and others),
Perhaps this UDF (user defined function) will be of some use to you...
Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As StringDim Cell As Range, Area As Variant
If IsMissing(Delimiter) Then Delimiter = ""
For Each Area In CellRanges
If TypeName(Area) = "Range" Then
For Each Cell In Area
If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value
Next
Else
ConCat = ConCat & Delimiter & Area
End If
Next
ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function
If you have never installed a UDF, the procedure is simple... press Alt+F11 to go into the VB editor, click Insert/Module from its menu bar and then Copy/Paste the above code into the code window that opened up. That's it. Go back to your worksheet put some text in, say A1, A2, A3, C1, D1, D2 and then type this formula into an unused cell...
=ConCat("-",A1:A3,C1,"HELLO",D1:D2)
and the contents of those cells (and the word "HELLO") will be concantenated together, in the order shown, with a dash between them. The delimiter (first argument) is required, but if you want to concatenate cells, cell ranges or text together without a delimiter, you can specify the empty string ("") as the first argument or simply omit it (but still use the comma as a place-holder in order to delineate the argument position). So, you could concatenate my above example cells and text, but with no delimiter between them, either like this...
=ConCat("",A1:A3,C1,"HELLO",D1:D2)
or like this (note the leading comma)...
=ConCat(,A1:A3,C1,"HELLO",D1:D2)
your choice. If you install the UDF into a workbooks, then the function will travel with the file if you distribute it to others. If you find this UDF useful and want it available for use on any worksheets that only YOU will work on, just install it in your personal.xls file... just remember, though, if you install it to your personal.xls file and use if from there, then the function will NOT travel with any worksheets you distribute to others (meaning cells using ConCat will produce a #NAME! error on their computers) unless, of course, they install the function to their own personal.xls file as well. If you want to pursue the personal.xls file route, and you don't now have one, you can find out how to create one here...
http://office.microsoft.com/en-us/excel-help/deploy-your-excel-macros-from-a-central-file-HA001087296.aspx
August 1st, 2010 at 4:01 pm
In the past I've used a UDF from Harlan Grove to stitch together multiple cells/ranges:
Function mcat(ParamArray s()) As String
'Copyright (C) 2002, Harlan Grove
'This is free software. It's use in derivative works is covered
'under the terms of the Free Software Foundation's GPL. See
'http://www.gnu.org/copyleft/gpl.html
'------------------------------------
'string concatenation analog to SUM
Dim r As Range, x As Variant, y As Variant
For Each x In s
If TypeOf x Is Range Then
For Each r In x.Cells
mcat = mcat & r.Value
Next r
ElseIf IsArray(x) Then
For Each y In x
mcat = mcat & IIf(IsArray(y), mcat(y), y)
Next y
Else
mcat = mcat & x
End If
Next x
End Function
which allows expressions like
=mcat("hi",(A1:A4,A6:D6,C2),"foo",A7:F9)
August 1st, 2010 at 4:59 pm
@Jason,
You might want to consider giving my ConCat UDF a try. My ConCat UDF provides the option to insert a common delimiter between the elements it concatenates together (think comma separated listing as one possible use) whereas Harlan's UDF does not provide such an option (meaning you would have to specify each delimiter individually is there was to be a common delimiter between concatenated items)... and when the delimiter is omitted from my ConCat UDF (true, you do still need to provide the comma as an argument place keeper though) or the empty string ("") is provided as the delimiter argument, then my ConCat UDF outputs the same text string as Harlan's UDF. And, while this is not all that important in the overall scheme of things, my ConCat UDF does all of this using two less code lines than Harlan's UDF.
August 1st, 2010 at 11:01 pm
If you're wondering why CONCATENATE() exists, since an ampersand is less ugly...
CONCATENATE() is less memory intensive and faster, but you'll only notice in non-trivial spreadsheets.
I'd still use the ampersand simply for the reason that it's easier to read and understand.
August 3rd, 2010 at 8:22 am
I asked about this on Chandoo's forum a while ago as I have always used "&" rather than CONCATENATE(). Apparently "&" is there from Lotus 123 compatability days:
http://chandoo.org/forums/topic/concatenate-or
Would be great if Microsoft could make CONCATENATE work over a range and with delimiters.
August 9th, 2010 at 5:54 am
I guess CONCATENATE is a handy function, but not sure where it had got that name - too difficult to remember the spelling to be able to direclty type in the cell. Therefore, I always use ampersand, instead of going Insert >>> Function search for concat and get CONCATENATE.
Regards,
Fuad
September 1st, 2010 at 5:40 pm
Hey Rick,
Thank you so much for posting the ConCat function. That is so useful, especially when I need to add multiple columns together or my data set fluctuates in size.
Brad