Excel - Concatenate cells "line by line"

July 2017

Issue


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 

Solution


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:

=combineCells(F5,G5)

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


Published by aakai1056. Latest update on May 1, 2012 at 12:27 PM by aakai1056.
This document, titled "Excel - Concatenate cells "line by line"," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).