Kioskea
Recherche
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
Read more 
Answer
+0
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

=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
+0
moins plus
Hi,

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

Best,

Duffy
Add comment
This document entitled « How to concanate two cells "line by line" » from Kioskea (en.kioskea.net) 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.