Excel - Concatenate cells "line by line"

October 2016


have one cell that has names separated with alt + enter. On the other cell, I have additional information on those names (also separated by alt+enter).

What I need to do is to concanate those cells "line by line". So instead of adding the 2nd cell to the end of the 1st cell, I need to match information on each line.

1st cell:
Name 1 
Name 2 
Name 3 

2nd cell:
Information 1 
Information 2 
Information 3 

Combined cell should be like this:
Name 1 Information 1 
Name 2 Information 2 
Name 3 Information 3 


You have to use a macro to do it.

Here is a custom function that can do it.
If you don't want function, you can use copy / paste special to convert to values or convert the function to a macro that can do the same.

Current usage would be some thing like this:


Public Function combineCells(rng1 As Range, rng2 As Range) As String

   Dim vFirst     As Variant
   Dim vSecond    As Variant
   Dim iIndex     As Integer
   Dim sNewValue  As String
   vFirst = Split(rng1, Chr(10))
   vSecond = Split(rng2, Chr(10))
   sNewValue = vbNullString
   For iIndex = LBound(vFirst) To UBound(vFirst)
      If (sNewValue <> vbNullString) Then sNewValue = sNewValue & Chr(10)
      sNewValue = sNewValue & vFirst(iIndex) & " " & vSecond(iIndex)
   Next iIndex
   If (sNewValue = vbNullString) Then sNewValue = ""
   combineCells = sNewValue
End Function

Thanks to rizvisa1 for this tip.

Related :

This document entitled « Excel - Concatenate cells "line by line" » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.