Merging data in cells and seperating by a comma in 1 cell

[haydenmeeks]

Hi there

I have been working and trying everything I can think of and using other examples to try and get this project finished but just can't seem to do it

I have worksheet with up to as many as 200 or 300 phone numbers in row 1. Each cell holds its own phone number. Each time I run this report or macro, the number of phone numbers will vary. Could be 10, could be 100.

What I would like to do is merge them all into 1 cell and seperate each phone number by a comma (no spaces) in the 1 cell, so I can right click and copy the data to another program (batch SMS) easily.
I know there can be a limit of numbers that a cell can hold so if this needs to be spread over a number of different cells thats ok. Provided the last character in a cell is a comma therefore I can do it sequentially, 1 cell after the other and copy and paste into the other program.

Any help with this would be greatly appreciated.

Many thanks guys
Hayden

 

[gallen]

Assuming the numbers start in A1 and continue on row 1:

(Make sure cell A1 is formatted to text)

Sub MergeNumbers()

Dim lc As Long 'last used column
Dim i As Integer

    'get last column
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    
    For i = 2 To lc 'start in 2nd column
        'Check next cell has a value
        If Len(Cells(1, i)) > 0 Then
            Cells(1, 1) = Cells(1, 1) & "," & Cells(1, i)
        End If
    Next i
    
End Sub

Edit: Make sure you try this on a copy of your data

 

[haydenmeeks]

Hey Gallen,

Thanks so much for taking the time to reply

I've tried that code and when I run it nothing happens at all..... not sure if it's me(highly probable).

Any thoughts

Cheers
Hayden

 

[gallen]

In which cell do your phone numbers start? I've tested this and it works for numbers in row 1

 

[haydenmeeks]

In which cell do your phone numbers start? I've tested this and it works for numbers in row 1

A1...

Do I need to fill any thing in that code, or copying and pasting it into a new macro in VBA work.

Sorry, I pretend like I have a clue, but I really dont...

 

[Rick Rothstein]

Here is another macro you can try which can handle a maximum of 400 numbers as written. The code assumes the data starts in cell A1 and goes across with no blank cells within the data and the output will be placed in cell A2)...

[table="width: 500"]
[tr]
	[td]Sub MergePhoneNumbers()
  Range("A2").NumberFormat = "@"
  Range("A2") = Replace(Trim(Join(Application.Index(Range("A1:GR1").Value, 1, 0))) & " " & _
                Trim(Join(Application.Index(Range("GS1:OJ1").Value, 1, 0))), " ", ",")
End Sub[/td]
[/tr]
[/table]

Note: It appears that the cell might not display the entire text string if it is quite long; however, you can copy the cell and all the true numbers will be copied even if they are not display in the cell itself (they are all there in the Formula Bar).

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MergePhoneNumbers) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[gallen]

Yes copy the code and paste it into the code window of the sheet with the numbers (Right Click the tab that has the sheet name and select "View Code" and past it into the window that appears.)

Then you need to run the macro.

 

[haydenmeeks]

OMG - proof I have no clue.

The numbers run down column A not in row 1.

As in
A1
A2
A3
.....

Sorry for wasting your time.

Hopefully you can help with this one

Cheers
hayden.

 

[gallen]

Rick reminded me of the cell limitation (32767 characters)

If your phone numbers total more than this then this code should allow for it:

Sub MergeNumbers()

Dim lc As Long 'last used column
Dim i As Integer, j As Integer


    
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    'Cells can hold a max of 32767 characters. If we exceed that we need to go to next cell
    j = 1 'Row number of cell
    
    For i = 2 To lc 'start in 2nd column
        If Len(Cells(1, i)) > 0 Then
            If Len(Cells(j, 1)) + Len(Cells(1, i)) > 32767 Then
                j = j + 1
                Cells(j, 1) = Cells(1, i)
            Else
                Cells(j, 1) = Cells(j, 1) & "," & Cells(1, i)
            End If
        End If
    Next i
    MsgBox "Done"
End Sub

 

[Rick Rothstein]

OMG - proof I have no clue.

The numbers run down column A not in row 1.

As in
A1
A2
A3
.....

Give this macro a try (output to cell B1)...

[table="width: 500"]
[tr]
	[td]Sub MergePhoneNumbers()
  Range("B1").NumberFormat = "@"
  Range("B1") = Replace(Join(Application.Transpose(Range("A1:A400"))), " ", ",")
End Sub
[/td]
[/tr]
[/table]

Note: It appears that the cell might not display the entire text string if it is quite long; however, you can still copy the cell and all the true numbers will be copied even if they are not display in the cell itself... they are all there in the Formula Bar and will be there when you paste them out to wherever you want to paste them to.