# How to concanate two cells "line by line"

Solved/Closed
Duffy - Apr 22, 2012 at 03:55 PM
Duffy - Apr 30, 2012 at 01:15 AM
Hello,

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:

http://speedy.sh/MMeUK/Concanate-Example.xlsx

Can anyone help me?

Duffy

## 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

=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```
Hi,

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

Best,

Duffy