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.

66 Responses

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.

I would like to know how could i concatenate text on my button that I have created on my worksheet.
For example I have Inserted a button on my worksheet and I want to concatenate text from 2 cells to my button; cells G1 and M1

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

I Think concatenate can be easier than &. You can concatenate many cells by typing =concatenate( and then holding down CNTL while clicking on the cells in the sequence you want to concatenate them. Excel automatically puts the commas in and this saves typing the &.

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…

Your macro UDF just works brilliantly. I was converting my excel data into chart prototyping software (mockuptiger) but it accepts transposed data not tabular and all data need to be comma separated. And the cells were 100 rows deep. I applied your formula
=concat(“,”,A1:A100) and boom it gave me what i needed. awesome.

@ Rick Rothstein, your UDF is a dream come true. I’ve been struggling with CONCATENANTE, TRIM, and SUBSTITUTE for hours trying to get it to do what your function does quite elegantly. Much thanks for this.

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.

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.

Hello, is there some function that allows the concatenation of a range of cells including the blanks as spaces. For example, I have a phrase divided into several cells so EACH letter occupies ONE cell, and spaces also occupy one space. All the functions I have tried dismiss blank cells, even the ones that have some sort of SKIPBLANKS argument, for example this one, http://www.vbaexpress.com/kb/getarticle.php?kb_id=817#instr. So something like BLUE HOUSE ends up concatenated as BLUEHOUSE. I’m using Excel 2010 so I don’t know if that has something to do.Some help would be greatly appreciated.

I could not find an edit button so sorry for the triple post, feel free to erase my previous posts. You see I have the X and Y positions in one sheet, the length in another and the actual info in another. The function itself will be used in a fourth sheet so that’s why I added the last comment. So starting position is comprised of 2 coordinates, each one inside a single cell. Again, if this is too much work just ignore this post.

> One more thing, if its not too much trouble. Is it possible to modify> it so that instead of a range it asks for a starting point and a length?

@Jeffrey,

This would change the code from a UDF to a macro… no trouble doing that, but you need to clarify a couple of things for me first. Instead of “starting point and length”, what if I set the macro up to process the selected range of cells? That way you could select all the cells in the row from the first to the last letter and then run the macro? I could also make the selection process as part of the macro if that would be better for you… that is, you would run the macro and the first thing it would do is select the range across the row containing your letters. Let me know if either of these ideas appeal to you. The other question I have is… now that it is not a UDF, it doesn’t know where to put its output. How did you want to handle that? I could have the macro ask you to select the output cell… would that be okay? Or do you have a reserved cell in mind where you always want the output to go to? If so, which cell?

Sure, it can be a macro, that’s not a problem. The thing with the range is that it works for some cases but not for others. Let me give you some background information. I managed to transform a data spool (.txt) to a matrix in excel using the “Import from Text” functionality, so that each character in the spool occupies a cell in the matrix (This is Sheet 2). Sheet 1 acts as a reference containing all the information needed to validate the info in Sheet 2. So based on the Reference Sheet I use your formula in Sheet 3 to validate that the “sections” of the Spool Sheet are where they are supposed to be and have the correct length and data type. The Reference Sheet contains cells with X position, Y position, length, info type (String, Number and DateTime), and others, for each of the sections that need validation.Considering this, it would be nice if the formula or macro considers X, Y and length in Reference Sheet, goes to the Spool Sheet to the specified coordinates and joins the amounts of cells stated in length. As you can see its pretty convoluted because after this is done, I have to convert the resulting string to the data type defined in the Reference Sheet for that “section” or “line” so that I can validate further. Hope I clarified the situation properly, if not I will gladly give more details about it.

I got a little confused reading that.;-) What I decided to do is leave it as a function and have you enter the sheet name, X value, Y value and Length (in that order). So, you would use a formula like this on your worksheet…

=JoinLetters(“Sheet1?,2,5,25)

where that would mean the letters are on the worksheet named “Sheet1? starting in cell E2 (X being Row 2 and Y being 4 for Column “E”) and running across for 25 cells. Here is the code to handle this input…

It’s working very well Rick but I’m encountering a side effect when I try to remove the following empty spaces. Example: The referenced length is 80 and the line itself contains 19 characters (16 letters and 3 spaces) after using your formula I use the LEN() function and it states that it has a length of 49. The JoinLetters string shows up something like this ‘Xxxxx xx Xxxxx Xxxx ‘. Basically 19 characters and 30 spaces. Its weird because its not showing a length of 80… ideally it’s LEN() should be 19 but that would involve including a space remover inside the JoinLetters function that mistakenly could take out the inner 3 spaces which are needed and not just the outer 30 spaces.

Some issues and solutions with concatenating things:
= >256 – Data longer than 256 characters might not transfer fully in certain actions, like edit/copy/worksheet. When you use & for text and long sentences or paragraphs, this can be a problem. If you can identify cells with >256 chars, these can usually be manually copy-and-pasted to the destination. Better than nothing.
=TRIM – an excellent function when combining words, especially when some are ends of sentences or paragraphs or you-just-don’t-know and may contain internal spaces. The A1&” “&B1… can give you double spaces. Surrounding this all with =TRIM(yourlistwith&here) will limit all spaces to just singles. Too bad there isn’t something like an option for single commas or hyphens, but that can be done with clever SUBSTITUTEs or maybe find-and-replace. And if you’re an old typist who believes there should be double spaces after the period of a sentence, doing a replace of . with . will work (if you just do it once!).
=IF – Sometimes the use of IF statements can be helpful in tweaking cell contents though it can become tedious. You might want to skip cells with the number zero in them, or otherwise conditionally include a cell, so just put an if(A4 30 explicit references, so the & approach can avoid this where a CONCATENATE or SUM function won’t let you get higher than A30 when you give it a list of A1,A2,A3… (yes, I know A1:A30 is the same but this is just for illustration – you’d run into the problem if there were more than 30 terms like B4,C5,A2,F18…)
= Click – for long or complicated arrangements, use your mouse and click on the specific cells in the desired order for CONCATENATE, by typing =CONCATENATE(,,) and so on and let the computer put in the right cell reference. If you then want to get fancy you can replace all the commas with &” “& and get rid of the CONCATENATE function.
=ADDRESS() – can be handy to build up a reference (A1) from certain calculations, especially in conjunction with ROW() and COLUMN() functions [do you know that just plain ROW() will tell you the row number of the cell in which ROW() is placed? Ditto for COLUMN(). Can be very handy.]
=INDEX()
=INFO()
=IF()
=INDIRECT() some of these have been mentioned previously
=OFFSET()
… and other functions can be useful in sophisticated gatherings of data.

Excel can be very powerful. I tell people I could build a house with Excel. It’s not just for Math.

When I joined (Con) two cells the value in one of which was in Superscript font with & the Superscript
disappears and normal font is shown. How to correct this. I am doing this for Algebra so the student see
X raised to 3 etc

When I initially commented I clicked the “Notify me when new comments are added”
checkbox and now each time a comment is added I get four e-mails with the same comment.
Is there any way you can remove me from that service? Many thanks!

Thank you for letting me know that you’re getting several emails when comments are added to the post.
If you still have a copy of one of those emails, there should be a link that lets you unsubscribe from the comments.
If that doesn’t work, please send me an email, and let me know what email address you used for the subscription, and I can delete it for you.

Does anyone know how to sort letters alphabetically within a single cell? I have concatenated a series of 4 or 5 grades (A – E) into one cell, delimited by a space in between each grade, and have managed to sort by pasting the result into another call as a value, then separating back out to separate cells before sorting by row. A bit long winded, does anyone have a neater solution ?

Hi, I am trying to merge two cells and format them but it doesn’t work the way I wish it would. I need for example cell A1 0.880567 and B1 0.0762768 to merge in the format 0.88 ±0,08. I would really appreciate it If someone could help me. I manage to merge them but I can’t formate the cell to have only two decimal points. So far I have =CONCATENATE(A1;C1;B1)with C1 being ±

This has been a fascinating string. I am very aware of the & and it’s powers with concatenation. Here is the scenario that I’ve been presented with and I have tried everything I know and I am still at a loss.

They would like it so that when they click in B1, they can run a macro so Excel places the City, State and Zip in the same cell, but on two lines having the City, State and Zip directly under the address (starting with Bronx in this case.)

I’ve tried everything, by separating all the information with concatenating into two separate cells. Since there is already a coma between the street and City bringing it back with the “Text to Column” feature is the easy part. The tough part is writing a macro that will do that for every line which all are unique.

The macro runs well, up until the Shift+ Enter, which is great manually, but doesn’t seem to work in macros.

HI, all good but is it possible to have a cell with say 100 in it as a number with a descriptive say m2 (metres squared or foot square for instance) with no formula in the cell needed as the 100 is manually entered but for the 100 in this cell to be used as part of the formula where it is being used as a multiplyer. I dont need the m2 to come accross. Others have asked this sort of question but I havent seen an answer by anyone. Looking for some help, thank you al in advance,
Norman