How many cells can you concatenate in excel

When joining a fairly small range, it's no big deal to enter all the references in the formula bar. A large range would be tedious to add, typing each cell reference manually.

Below you will find 3 methods of quick range concatenation in Excel. Here are the detailed steps:. When you need to concatenate a huge range consisting of tens or hundreds of cells, the previous method is not fast enough because it requires clicking on each cell.

As the result, you will have an array of numbers to be concatenated. Delete the curly braces surrounding the array values. Whichever method you use, the concatenated value in C1 is a text string notice its left-alignment in the cell , although each of the original values is a number. Method 3. Use the Merge Cells add-in A quick and formula-free way to concatenate any range in Excel is to use the Merge Cells add-in for Excel with the " Merge all areas in selection " option turned off, as demonstrated in Combine the values of several cells into one cell.

When you concatenate a text string with a number or date, you may want to format the result differently depending on your dataset. We have already discussed one such formula in the beginning of this tutorial that concatenates text and date.

I will remind you that when combining a text string and date , you have to use the TEXT function to display the date in the desired format. A few more formula examples that concatenate a text value and number follow below:. You can find the detailed steps illustrated with formula examples and screenshots in the How to split cells in Excel tutorial. The Merge Cells tool works with all Excel versions from to and can combine all data types including text strings, numbers, dates and special symbols.

Its two key advantages are simplicity and speed - any concatenation is done in a couple of clicks. Incorrect instructions. Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. Thank you for your feedback! Argument name. The first item to join. The item can be a text value, number, or cell reference. Additional text items to join. You can have up to items, up to a total of 8, characters. VBA Loops. Privacy Policy Sitemap. Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster.

Wow this is supremely helpful, saved me A LOT of time! Thanks man. Can someone help me with this formula or similiar please? Thanks a lot for the code and the explanation. Many thanks for Multiple option — much appreciated. Hi, Does anyone know a way to do the following: Concatenate the values of several cells into a single cell and separate them with any delimiter of your choosing.

Thanks, nice little function. I am looking to do this same thing. Have you figured out how to do this? One of the solutions without VBA is submitted above. Hey Cornan.. I have edited the tutorial accordingly.

I was finding for auto update concatenate. Thanks for VBA code. This is great — thanks! This completely saved my ass today creating contact list spreadsheets to import elsewhere!!!! This did exactly what I needed it to in combination with Dynamic Ranges. Thanks for sharing such wonder ful trick. It works great!

I would like for the code to locate a cell, based on a certain text, then select everything in the list below that cell. Is that something easily doable?

Thanks for the interesting article. Unfortunately, as far as I can see, none of the methods actually does what I want. I am writing an accounting spreadsheet, and I want a column that shows an error message if there is anything unexpected in the columns where I enter data, or in the calculation columns. I want to simplify this by setting up some columns, each of which detects a particular error, and generates an appropriate message if it finds it.

This will make it much easier to add more error checking in future — just insert more columns in this part of the sheet. In this scenario, the error message column the one that is always visible needs to be a concatenation of all the individual error columns.

The problem I foresee here is that code generally executes when the user clicks a button; formulae execute when you change a cell on which they depend. If I set up a VBA program to execute every time I enter data, and it computes this for every line of the spreadsheet, it introduces a huge processing overhead! So this leaves Power Query. If as I suspect I need to click something to refresh my query in order to check for errors, then it suffers from the same problem as VBA.

Thank you for bringing it to my attention. Maybe I need to migrate to Excel The bugs mostly concerned the changed behaviour of SUMIF when the data range and criterion range were different lengths. This may be a bit out of date now.

However, if the above VBA is adjusted, it can be used to return the concatenated strings as a normal formula, enter something like the following into a VBA module Alt-F11, Insert-Module :. Cells r2, c2 Next r2 Next c2 End If. Like this Instant Air Would be one line remaining all are in one line with commas how it is possible can you let me know.

Does it have something to do with Excel settings? There were very useful tips. Thanks a lot. It was really handy. Then I have to change all of them to commas for it to work correctly. With justify, you need to make the cells where you text enough wide so that the entire text can be merged into one cell.

Ahhh, the text has to be arranged in a column for it to work! That appears to be the fastest option, but can it be done with Justify on text across a row?