How can I convert excel file (with mobile no. s) to "," comma separated text file?

My issue is that I have a excel file with many mobile no. from A1 to A1000 in excel file. I need to create an "," comma separate text (e.g. 12345678,23456789,56789012)


I first save as tab delimited text file.
Then, I use find and replace from MAC AIR editor to add "," after each mobile no.
From my Mac textedit editor Mac, it looks like
12345678,
23456789,
56789012,
..etc

However, it seems that there is an end of line at each line.
It doesn't accepted by my SMS tools.

Please provide a smart solution.
Gordon TinIT ManagerAsked:
Who is Participating?
 
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Gordon Tin,

If your expecting coding then please find the same, your datas should be in A column output will be received in C1 on the same sheet,

if you want to change output cell, you can modify

Just copy and paste the code in to your file and Run the Macro.

Sub Jointext()
Dim I As String
Dim Text As String
Dim Lastrow As String
Range("A1").End(xlDown).Select
Lastrow = ActiveCell.Address
For Each Cell In Range("A1", Lastrow)
    If I = "" Then
        I = Cell.Value
    Else
    I = Text & "," & Cell.Value
    End If
    Text = I
    Next
    Range("A1").Select
    Range("C1").Value = I

End Sub

Open in new window

0
 
NorieVBA ExpertCommented:
Gordon

Not sure if you are looking for code but if you are try this.
Sub CreateSMSFile()
Dim strFilePath As String
Dim strFileName As String
Dim FF As Integer
Dim arrNos As Variant

    strFilePath = "C:\Test\"
    strFileName = "SMSNos.txt"

    With Sheets("Sheet1")
        arrNos = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
    End With

    FF = FreeFile()

    Open strPath & strFileName For Output As #FF
    Print #FF, Join(Application.Transpose(arrNos), ",")
    Close #FF

End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
Copy and transpose your values onto a separate sheet in Excel so that each number is in a separate column rather than rows. Then with that sheet active, do save as and choose csv from the File Type list.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Gordon Tin

Please find the attached sheet with macro enabled, you just need to put this funtion/formual on attached spreed sheet to the get the exact output what your expecting, Joinfunction(A2:A1000,",") (Note: selected your range based on the your data)

Thanks!
Book1.xlsm
0
 
NorieVBA ExpertCommented:
Just realised there's a small typo in the code I posted, here's the corrected version.
Sub CreateSMSFile()
Dim strFilePath As String
Dim strFileName As String
Dim FF As Integer
Dim arrNos As Variant

    strFilePath = "C:\Test\"
    strFileName = "SMSNos.txt"

    With Sheets("Sheet1")
        arrNos = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
    End With

    FF = FreeFile()

    Open strFilePath & strFileName For Output As #FF
    Print #FF, Join(Application.Transpose(arrNos), ",")
    Close #FF

End Sub

Open in new window

0
 
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
I have provided the best solution as per the query raised by the requested or Author,

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.