arrange IP's in Excel Sheet.

Dear All,

I have an excel sheet which included more than 900 IP’s as the attached.

I need to take all the IP’s and make them with the following format:

10.10.1.1,10.10.3.3,10.11.12.13 …

I need to add a “,” between each IP without any space.

Can you help me doing this please? And how?
Book1.xlsx
ACS2012Asked:
Who is Participating?
 
TheAvengerConnect With a Mentor Commented:
If this is still not what you are looking for, attached is a file with some formulas in column B which can help you. Note that B1 has a formula different from the others (B2, B3, etc.) The final result is in the last cell in the column B, in this case in B3
Book1.xlsx
0
 
TheAvengerCommented:
Download Notepad++ from here: http://notepad-plus-plus.org/download/v6.1.6.html
Copy the cells from Excel and paste them in a new text file in Notepad++
Press Ctrl+H in Notepad++ which will bring up the replace dialog. Enter:

In "Find what": \n
In "Replac with": ,
Make sure Extended is selected in the Search Mode
Press Replace All

You will now have the list in your desired way. You can easily copy paste it back in Excel if you need it
0
 
ACS2012Author Commented:
anything I can do from excel without a 3rd party?
0
 
TheAvengerCommented:
You can do it with a formula. There is a nice guide here: http://www.get-digital-help.com/2011/02/09/concatenate-a-cell-range-without-vba-in-excel/. Your case is described under "Create delimiting character"
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.