[Excel] Concatenate cells "line by line"
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.
See also
Knowledge communities.
