Excel format

NoSparks · #3 05-12-2017, 08:33 AM

Don't know what you're actually working with or its layout so have assumed IP addresses are in column A of Sheet1 starting in A1 and write the result to C1.
Setup a sample test sheet for experimentation.
I suspect the C1 cell will contain more characters than Excel can display but you should be able to copy and paste the entire cell value provided it doesn't exceed 32,767 characters.

Code:

Sub reFormat()
    Dim lr As Long, i As Integer, str As String
    Dim arr As Variant

    lr = Cells(Rows.Count, "A").End(xlUp).Row
    arr = Sheets("Sheet1").Range(Cells(1, 1), Cells(lr, 1)).Value
    str = Chr(39)
    For i = LBound(arr) To UBound(arr)
        str = str & arr(i, 1) & Chr(39) & Chr(44) & Chr(39)
    Next i
    str = Left(str, Len(str) - 2)
    Range("C1").Value = str
End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA to Format a Table Column in Excel	Sgt Rock	Excel	0	02-14-2015 02:54 PM
Date format in excel vba	LaercioNB	Excel	3	08-09-2013 06:33 AM
excel 2010 to format a date	jassie	Excel	1	03-28-2013 02:33 AM
Need help in format or link in excel	handsome1968	Excel	1	05-01-2010 09:20 AM
Format Excel to look like Word	Kilconey	Excel	1	04-30-2010 09:42 AM