View original (French)

Concatenate a range of cells

Malgven -  
Vahde Posted messages 3 Registration date   Status Member Last intervention   -
Hello,

I would like to concatenate a range of cells in Excel so that my data contained in different cells are grouped into a single cell and separated by a comma.
My data consists of numbers with decimals separated by a point (e.g., 5.45) and I would like them to remain as such during the process.

I wanted to create an InputBox to select the range of cells I want to merge into one, but I'm completely stuck and can't get this macro to work!

Does anyone have an idea?

Thank you in advance.

Configuration: Windows / Firefox 45.0

2 answers

  1.
    Gyrus Posted messages 3360 Status Member 526
     
    Hello,

    Try it like this 
    Sub Test()
    Dim ref As Range, Cel As Range
    Dim Texte As String
     Set ref = Application.InputBox("Please select the cells on the sheet", Type:=8)
     For Each Cel In ref
     Texte = Texte & Cel.Value & ", "
     Next Cel
     MsgBox "Concatenated values: " & Left(Texte, Len(Texte) - 2)
    End Sub

    See you!
    1
    1. Malgven
       
      Hello,

      Thank you very much for your reply!
      It's almost working.
      Indeed, I’m getting the result in the Box while I would like it to be in a cell so I can copy-paste it into another software.

      Do you have (any) ideas?

      Thank you!
      0
      1. Gyrus Posted messages 3360 Status Member 526 > Malgven
         
        I didn't indicate the result in a cell because I don't know which cell you want to place it in.

        Example with the result in A1:
        You replace
        MsgBox "Concatenated values: " & Left(Text, Len(Text) - 2)
        with
        Range("A1")=Left(Text, Len(Text) - 2)

        Best regards.
        0
      2. Malgven > Gyrus Posted messages 3360 Status Member
         
        Yes, that works, thank you very much!

        And how can I make the box put the result in the active cell? :o
        0
      3. Gyrus Posted messages 3360 Status Member 526 > Malgven
         
        Activecell=Left(Text, Len(Text) - 2)

        Amazing, isn't it?

        See you!
        0
      4. Malgven > Gyrus Posted messages 3360 Status Member
         
        Pff! It's perfect!
        Thank you very much.
        All by myself, I really drove myself crazy for simple solutions in the end.

        Thanks again!
        0
  2. Vahde Posted messages 3 Registration date   Status Member Last intervention  
     
    Hello,

    The subject is a bit old, but I have a question.
    I would like to replace the "," between the concatenated cells with a line break ALT+ENTER.

    How can I do that, please?
    0