This forum was created to help business owners just like you that sell on eBay, Amazon and through your own eCommerce Site, with the simple ethos that:
"Selling an item online is easy, so is a couple of items, but making living from a business that sells online, well that's something different entirely."
I don't pretend to have all the answers and I know that "together we're stronger" and if you consider the wealth of knowledge that we each posses, using this forum can help us all. We each have a huge amount of experience and provide our own perspectives to any questions & challenges that we may have..
Get help right now
Registration is free and you can ask your question straight away.
Excel Concatenate Function (concat) - Combine Thousands of Cells
You are here:
Please consider registering
I found this a while ago and its exceptionally useful for concatenating thousands of cells together http://chandoo.org/wp/2008/05/…..el-concat/
You can use it like this:
Function concat(useThis As Range, Optional delim As String) As String
‘ this function will concatenate a range of cells and return one string
‘ useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = “”
If delim = Null Then
dlm = “”
dlm = delim
For Each cell In useThis
if cstr(cell.value)”" and cstr(cell.value)” ” then
retVal = retVal & cstr(cell.Value) & dlm
If dlm “” Then
retVal = Left(retVal, Len(retVal) – Len(dlm))
concat = retVal
July 18, 2012
Yes, when you hit the cell text or formula limit in a single cell, use the above.
It’s not so much of a problem in Excel 2007 as they made the formula lengths much longer, but when you need to concatenate hundreds if not thousands of cells together, this works amazingly.
I used this on a parts compatibility database as it was easier and more maintainable for a client which had around 2800 separate cells to join up.
Most Users Ever Online: 298
Currently Browsing this Page:
Newest Members: Xavier, David Daen, nic_ism, Michelle Arnold, dealline, ojilcasi, leemcv, bighomeuk, Phil Taylor, dogendorf, Beckie Sims, Andy Womack, da, Piano, NiSo
Administrators: Dave Furness (71), Matthew Ogborne (218)