Ask a question Report

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

Duffy - Latest answer on Apr 30, 2012 01:15AM

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

Answer

rizvisa1 4230Posts Thursday January 28, 2010Registration date ContributorStatus May 7, 2013Last seen Apr 22, 2012 05:26PM

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

Add comment

Answer

Duffy Apr 30, 2012 01:15AM

Hi,

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

Best,

Duffy

Add comment

Reply to this topic

Ask a question

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.

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

Not a member yet? sign-up, it takes less than a minute and it's free!

Not a member yet?

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