Ask a question Report

How to concanate two cells "line by line" [Solved]

Duffy - Latest answer on Apr 30, 2012 01:15AM
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?
Read more 

moins plus
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
Add comment
moins plus

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


Add comment
This document entitled « How to concanate two cells "line by line" » from CCM ( 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.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.