Excel format

mj8mj4 · #1 05-11-2017, 11:54 PM

hey guys

I have a list of 1000 IP address that I need to use them in python code but I need them in specific format like this

192.168.1.1
192.168.1.2
192.168.2.1

I need them like this :

'192.168.1.1','192.168.1.2','192.168.2.1'
basically I need this format '','',''

any Idea ?

mj8mj4 · #2 05-12-2017, 12:04 AM

whats the steps or the formula ? please

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

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