Combine Cells in Excel Without Concatenate

image 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

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

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.

_____________

54 comments to Combine Cells in Excel Without Concatenate

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

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

  • Rick Rothstein (MVP - Excel)

    @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 String
    Dim 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

    • Jaime Hanson

      Rick, I just found your UDF to concatenate cells and add a common character between each. THANK YOU for the timesaver!!

    • Cyndy Kauhi

      Oh my gosh Rick - your macro was a lifesaver for me! Merged 2300 names with a semi-colon delimiter in seconds! Thank you!

    • Nilesh

      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.

    • James

      THANKS MUCH FOR THIS!

      I was copying from a website with .aspx and it copied each word to a separate line in Word or Excel, even with paste as plain text. How ugly!

      I was able to concatenate a range of rows in Excel, and pasted one page at a time into Excel, then copied result from one cell into Word, for easy word wrap & editing.

  • Jason Morin

    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)

  • Rick Rothstein (MVP - Excel)

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

  • Mark Nold

    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.

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

  • Fuad

    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

  • Brad

    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

  • Dickson

    I am trying to apply both functions to append:
    "123? and "000001?
    it turns out returning "1231? instead of "123000001?..
    is there anyway i can get it to return "123000001? ?

  • Dickson, the cell with "000001? is formatted to show the leading zeros, but the value in the cell is really a "1?, so that's what you see after combining the values.

    To keep the formatting, you could use the TEXT function:
    =B2&" "&TEXT(E2,"000000?)

  • Jeffrey

    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.

  • Rick Rothstein (MVP - Excel)

    @Jeffrey,

    Assuming you mean the cells with the individual letters are in a contiguous range of cells across a row, give this one-liner (albeit a long one) UDF (user defined function) a try...

    Function JoinLetters(RowRange As Range) As String
    JoinLetters = Replace(Replace(Join(WorksheetFunction.Index(Range("A5:J5"). _
    Value, 1, 0), Chr(1)), Chr(1) & Chr(1), " "), Chr(1), "")
    End Function

    • joe

      Rick:
      Related topic:
      When I merge two cell using Ampersand or Concatenate, EXCEL's "FIND" function cannot find the merged text.
      Example: A1 = john; A2 = smith; A3 = johnsmith. Then FIND cannot find johnsmith. Further, it cannot even find john or smith in johnsmith. All cells are formatted as Text.
      Please help.

  • Jeffrey

    Hi Rick, thanks for replying so quickly. I tried to use the function but it didn't work, maybe the A5:J5 part is affecting the calculations?

  • Jim Cone

    Jeffrey,
    Rick's function worked for me.
    I used the RowRange variable to replace the A5:J5 location in the function...

    Function JoinLetters(RowRange As Range) As String
    JoinLetters = Replace(Replace(Join(WorksheetFunction.Index _
    (RowRange.Value, 1, 0), Chr(1)), Chr(1) & Chr(1), " "), Chr(1), "")
    End Function

  • Jeffrey

    Excellent, thanks to the both of you. This function will help me a lot.

  • Jeffrey

    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

    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.

  • Rick Rothstein (MVP - Excel)

    > I used the RowRange variable to replace the A5:J5 location in the function...

    @Jim Cone

    Hmm! Bad editing by me on going from a macro, originally, to a UDF instead. Thanks for picking up on that Jim... much appreciated.

  • Rick Rothstein (MVP - Excel)

    > 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?

  • Jeffrey

    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.

  • Rick Rothstein (MVP - Excel)

    @Jeffrey,

    What is X and Y... Row and Column, Column and Row, something else?

  • Jeffrey

    X represents the Row and Y the Column.

  • Rick Rothstein (MVP - Excel)

    @Jeffrey,

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

    Function JoinLetters(WS As String, X As Long, Y As Long, Length As Long) As String
    JoinLetters = Replace(Replace(Join(WorksheetFunction.Index _
    (Worksheets(WS).Cells(X, Y).Resize(, Length).Value, 1, 0), _
    Chr(1)), Chr(1) & Chr(1), " "), Chr(1), "")
    End Function

  • Rick Rothstein (MVP - Excel)

    > ...and Y being 4 for...
    @Jeffrey (again),

    Sigh! That was supposed to by "...and Y being 5 for..."

    Sorry for any confusion my typo may have caused.

  • Jeffrey

    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.

  • Rick Rothstein (MVP - Excel)

    @Jeffrey,

    Does this version work the way you want?

    Function JoinLetters(WS As String, X As Long, Y As Long, Length As Long) As String
    JoinLetters = Trim(Replace(Replace(Join(WorksheetFunction.Index( _
    Worksheets(WS).Cells(X, Y).Resize(, Length).Value, 1, 0), _
    Chr(1)), Chr(1) & Chr(1), " "), Chr(1), ""))
    End Function

  • Jeffrey

    Works like a charm Rick, thank you very very much for your time and expertise. You have given me a significant head start in this endeavor. I'm off to battle with the data types validations.

  • Contextures Blog » Excel Formulas Show in Cell

    [...] year, I showed that you could combine text in Excel by using the ampersand (&) operator, instead of the CONCATENATE function. That makes it much [...]

  • aseem

    How to combine common cells into one cell.
    eg a1 to a7 have the same content like docno1 but i want to show docno1 at a1 only remaining i want to show blank spaces .how can i do this.

  • ozibear

    How to I make a cell reference (or part of the reference) be the value of a cell

    eg let cell F8= 123 and if C4 has a value of "F8" , and I want E4 to be equal to the value of (the cell refered to in C4) , ie E4 looks only at C4 to then be refered to F8 to get its value of 123

    I want E4 to be something like =(C4) where something other than these parentheses is required

  • @ozibear,

    Try putting this formula in E4...

    =INDIRECT(C4)

  • Tom

    I was searching for a little help then I found this page, and I received a lot of help. Thank you for taking the time to post.

  • TXtom

    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

  • In cell A10 I write 'X' In Cell A11 I write '3'. I format cell A11 and select Superscript.In Cell A12 I write '=A10&A11' The result is 'X3' But '3' is not in Superscript.

  • Douglas Milby

    If I use a range were a number of cells will be blank and the quantity varies from use to use,
    how can I keep from getting this:

    Rob B, Michael B, Ben E, Bret O, Chris R, , , , , , , ,

  • Lo, these many years later I remembered Rick's UDF at the beginning of this post and have added it to my personal addin. Don't know why I waited so long. Thanks Rick!

  • TheGuy

    Any way to concatenate a cell with itself without creating a loop ? (Only update when the other cell changes and add it to it current text)
    Here is what I would like to do...

    Example:
    - B2 dropdown list contains the item list dog, cat, cow, chicken
    - I select "cow" from the drop down
    - B3 gets populated with "cow"
    - I select dog from the drop down
    - B3 gets populated with "cow, dog"
    - I select "cow" again
    - B3 does not get updated as the string already contains "cow"
    - I select "chicken" from the drop down
    - B3 gets populated with "cow, dog, chicken"

    Effectively I'm trying to create a check box list using a drop down :P
    So far I'm stuck with reference loops...

  • to

    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.

  • Tina Harrison

    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 ?

  • Tanzil Sayyed

    How to use concatenate if I want to write 2nd Sentence below 1st sentence in a same cell.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>