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

haydenmeeks

New Member
Joined
Sep 28, 2016
Messages
5
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

Well-known Member
Joined
Jun 27, 2011
Messages
1,931
Assuming the numbers start in A1 and continue on row 1:

(Make sure cell A1 is formatted to text)

Code:
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
 
Last edited:

haydenmeeks

New Member
Joined
Sep 28, 2016
Messages
5
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

Well-known Member
Joined
Jun 27, 2011
Messages
1,931
In which cell do your phone numbers start? I've tested this and it works for numbers in row 1
 
Last edited:

haydenmeeks

New Member
Joined
Sep 28, 2016
Messages
5
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... :LOL:
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,502
Office Version
2010
Platform
Windows
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)...
Code:
[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.
 
Last edited:

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,931
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

New Member
Joined
Sep 28, 2016
Messages
5
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

Well-known Member
Joined
Jun 27, 2011
Messages
1,931
Rick reminded me of the cell limitation (32767 characters)

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

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,502
Office Version
2010
Platform
Windows
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)...
Code:
[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.
 
Last edited:

Forum statistics

Threads
1,081,578
Messages
5,359,740
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top