Excel 2010 merge / concatenate

Hi Darlene,

If you data is in four rows in column A, say A1 through A4 inclusive, and you want the contents of those cells to be concatenated into another cell (with semi-colons in between), then enter this in that cell:

=A1&";"&A2&";"&A3&";"&A4

Hope that helps,

Alan.

You can use the CONCATENATE() function, for example, create a formula in cell A1 that looks like the following to merge the contents of cells A2, A3, A4, A5.

=CONCATENATE(A2,";",A3,";",A4,";",A5)

---

»bp

I have many rows - is there any other way besides this manual approach.

Try below:
Change the necessary variables which suits you:

Sub CombineRange()
Dim Ws As Worksheet
Dim LastR As Long
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
Set Ws = Worksheets("Sheet1") 'Change the sheet name here
LastR = Ws.Range("A" & Rows.Count).End(xlUp).Row ' Change the last row Column here
Set InputRng = Ws.Range("A2:A" & LastR) 'Change the range you want to combine
Set OutRng = Ws.Range("B2") 'Change the cell where you want result to be
Application.ScreenUpdating = False
OutStr = ""
For Each Rng In InputRng
    If OutStr = "" Then
        OutStr = Rng.Value
    Else
        OutStr = OutStr & "; " & Rng.Value
    End If
Next
OutRng.Value = OutStr
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Attach is sample....
Darlene_Shepher_Combine-Range.xlsm

I wrote an article on this.  You can pick up the code in the article:
https://www.experts-exchange.com/articles/7811/A-Better-Concatenate-Function.html

I seem to remember finding something with the paste special multiplier that supported this - does that ring a bell with anyone?  Thank you

There is a "Paste Special Multiply" option when copying and pasting a range of cells, but that doesn't merge any of them.  It just applies a factor multiplied times the original copied values before pasting them.  So you can easily scale a set of values, perhaps increasing them all by 25%, etc.

---

»bp

Darlene,

Did you try my solution, please let me know if it combines all in range?

Hi Darlene,

I don't think you can use Paste-Special-Multiply to achieve the outcome.

The quickest and easiest option is as per my reply to your previous post.

Hope that helps,

Alan.

If you just want to stay with formulas while using a small helper VBA function, here is an implementation of the TEXTJOIN() function that you can then use in your data sheet in a formula.  It allows easy concatenation of one of more ranges of cells, which makes the process pretty painless.  Just another option to consider.

I've included an example spreadsheet with the macro added to it showing how easy it is to combine multiple ranges and add a delimiter.  You can also skip or include blank cells as needed with a parm.  Notice the merged cell using the formula:


Credit to Combining Text With The CONCAT and TEXTJOIN Excel Functions — The Spreadsheet Guru for the macro code.  As mentioned there, the parms to this function are the same as the new TEXTJOIN function in Excel 2016 if you are an Office 365 subscriber (I am not).

1. Delimiter - This is a designated character or string of characters that will be inserted in between each "Text" or "Cell" input provided.
2. Ignore_Empty - This is a TRUE or FALSE input that asks if you want to ignore blank values. If FALSE, you run the risk of have two delimiter characters next to each other (for example, a result of "Apple,Orange,Banana" could end up being "Apple, Orange,,Banana")
3. Text - the text item(s) to be joined. This can be a text string, or array of strings (such as a range of cells).These are the same exact inputs you would use with the CONCATENATE() or CONCAT() functions.

TEXTJOIN.xls

---

»bp

Thank you I believe the textjoin is only in 2016.  I am using 2010.  I had hoped I didn't have to use a macro because they will export and receive a template.  Anyway I just thought I had done this before.   I will follow up on the previous answers - Thank you all for your help

Yes, TEXTJOIN() is only built in to 2016, that's why I included it as a macro, but understand if you want to stick with formula.

Okay, without a macro I think there have been a number of suggestions on the formula approach here, let us know how it goes.

---

»bp

Hi Darlene,

No need to use and VBA / macros - did you try the solution I outlined right near the start?

If it doesn't do what you want, please post back and tell us why and we can amend.

Thanks,

Alan.

Hi All,
I have solution for this, but you need a ms word application to this...
Very easy follow my step

1. Copy All Data
2. Paste in Word
3. Convert table to Text
4. Replace "^p" with "; "

I have attached the screenshot with brief explanation
Testing.jpg

Bill Prew - I don't see the macro - just a formula which I assume only works in 2016 - am I missing something? - Wishing for this I had 2016 it seems to be the easy answer.
Arunraj Sekar - Thank you but I don't want to use word they are already in excel
Alan3285 - I will check if copy works with the limitations
Shums - I will also try the Macro - is there an easy step to copy a macro - as I said they will have an exported spreadsheet open - now I need to provide a formula or the Macro - obviously it would be easier to provide them the formula.
aikimark - I note your article.
Thanks again to all

The macro was in the attached spreadsheet to my earlier post.  Here is the code that I placed in that workbook:

Public Function TEXTJOIN(Delimiter As String, Ignore_Empty As Boolean, ParamArray Text1() As Variant) As String

Dim RangeArea As Variant
Dim Cell As Range

'Loop Through Each Cell in Given Input
  For Each RangeArea In Text1
    If TypeName(RangeArea) = "Range" Then
      For Each Cell In RangeArea
        If Len(Cell.Value) <> 0 Or Ignore_Empty = False Then
          TEXTJOIN = TEXTJOIN & Delimiter & Cell.Value
        End If
      Next Cell
    Else
      'Text String was Entered
        If Len(RangeArea) <> 0 Or Ignore_Empty = False Then
          TEXTJOIN = TEXTJOIN & Delimiter & RangeArea
        End If
    End If
  Next RangeArea

TEXTJOIN = Mid(TEXTJOIN, Len(Delimiter) + 1)

End Function

Select allOpen in new window

---

»bp

Hi Darlene,

The limitations I mentioned will apply to any / all solutions - they are related to the maximum number of characters in a cell in Excel, it doesn't matter how you get them there.

Thanks,

Alan.

EE requested assistance in closing this question.