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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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
LearnReporting 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LearnReporting 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
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
LearnReporting Automation ExpertCommented:
I have provided the best solution as per the query raised by the requested or Author,

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.