How to concanate two cells "line by line"

Duffy - Apr 22, 2012 at 03:55 PM
 Duffy - Apr 30, 2012 at 01:15 AM

I 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
Name 1 Information 1
Name 2 Information 2
Name 3 Information 3

This excel sheet illustrates the problem and the desired result:

Can anyone help me?


2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 22, 2012 at 05:26 PM
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

I forgot to thank you. The macro works like a charm :) Consider it solved.