View original (French)

Concatenate a range of cells

Malgven -  
Vahde Posted messages 3 Registration date   Status Membre 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 réponses

Réponse 1 / 2
Gyrus Posted messages 3360 Status Membre 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
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
Gyrus Posted messages 3360 Status Membre 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
Malgven > Gyrus Posted messages 3360 Status Membre
 
Yes, that works, thank you very much!

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

Amazing, isn't it?

See you!
0
Malgven > Gyrus Posted messages 3360 Status Membre
 
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
Réponse 2 / 2
Vahde Posted messages 3 Registration date   Status Membre 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