Concatenate telephone nos. from cells in a column.
Posted by Bijay Tay on February 18, 2001 4:48 AM
I need to put about 30 telephone numbers from each cell in a column into a cell. Each tel no. to be separated by a space. I can do that by concatenation formula but it is very slow. Is there a way to do it in a few steps?
Posted by Dax on February 18, 2001 8:13 AM
You could use a custom function like this. Open the VB editor (Alt + F11), click Insert, Module and use this code.
Function SpecialConcatenate(rnge As Range) As String
Dim r As Long, col As Integer
For c = 1 To rnge.Columns.Count
For r = 1 To rnge.Rows.Count
If rnge.Cells(r, c) <> "" Then
SpecialConcatenate = SpecialConcatenate & _
rnge.Cells(r, c).Value & " "
End If
Next r
Next c
End Function
Now if you use the function "=SpecialConcatenate(A1:A30)" it will return everything within the range separated by a space.
Regards,
Dax.
Posted by Dave Hawley on February 18, 2001 3:43 PM
Have you tried the "Text to Columns" wizard under Data on the menu bar ?
Dave
OzGrid Business Applications
Posted by Faster on February 19, 2001 7:45 AM
Sub MyJoin()
'assume start of list is A1
Range("A1").Select
Dim MyCell
MyCell = ""
Do While Selection <> ""
MyCell = Selection & " " & MyCell
Selection.Offset(1, 0).Select
Loop
'Cell to output to
Range("B1") = MyCell
Range("B1").Select
End Sub
Posted by Dax Monterellio on February 19, 2001 12:05 PM
Was there something wrong with the answer I gave? It allowed the user to select any range, was faster then the other macro given and didn't use text to columns to try and join data in different rows.
Regards,
Dax.
Posted by Bijay Tay on February 19, 2001 4:16 PM
Dax, Your advise is fantastic, I followed your instruction to the letter. Despite the fact that I had never used VBA, I just press Alt+F11 and paste the code onto it, and it works.
Many Thanks
Bijay
