[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

arrange IP's in Excel Sheet.

Posted on 2012-09-02
4
Medium Priority
?
661 Views
Last Modified: 2012-09-02
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
0
Comment
Question by:ACS2012
  • 3
4 Comments
 
LVL 20

Expert Comment

by:TheAvenger
ID: 38358539
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
 

Author Comment

by:ACS2012
ID: 38358551
anything I can do from excel without a 3rd party?
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 38358560
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
 
LVL 20

Accepted Solution

by:
TheAvenger earned 2000 total points
ID: 38358568
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
If something goes wrong with Exchange, your IT resources are in trouble.All Exchange server migration processes are not designed to be identical and though migrating email from on-premises Exchange mailbox to Cloud’s Office 365 is relatively simple…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question