Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to remove the last digits from a list of IP's in excel

Posted on 2011-09-14
7
Medium Priority
?
400 Views
Last Modified: 2012-05-12
I have a list of ip's about 160 in one colum in an excel spread sheet.

I need to remove the last .xxx from the list of ip's and add a .0 at the end

example 192.168.1.100 - 192.168.1.0

so how can I preform this to change all the ips to remove the last 3 digits and replace it with a 0.

I really don't know excel.  

thanks

0
Comment
Question by:rdefino
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 36536684
You can get the result with a formula in another column, e.g. with data in A2 down use this formula in B2 copied down

=IF(A2="","",LEFT(A2,LEN(A2)-3)&0)

If you want to replace the original results with those do this:

Copy column B then use Edit > Paste Special> Values to change column B to fixed data rather than formula results....now delete column A

regards, barry
0
 
LVL 25

Expert Comment

by:Brian B
ID: 36536696
This is a bit tricky, because as far as I know, the FIND() function onlys finds the first occurences of a character within a string. So it would require a nested set of MID() and FIND() functions to find the third dot.

I'm still working on it, but maybe that will get others started if someone else wants to contribute.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 36536699
In column B (or add one if you have it filled with other stuff), add:
=LEFT(A1,10) & "0"
and copy/paint it down the last row. You can then copy/paste special->values to the column and delete the extra one.

If your ip domain changes (for example, 10.0.0.x) you need a different formula. But if they're all in the 192.168.1.x (or even up to 192.168.9.x) then this will do.
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 25

Accepted Solution

by:
Brian B earned 500 total points
ID: 36536813
Okay, I got it. This will always work. If the column of IPs is in column A:

=LEFT(A1,FIND("x",SUBSTITUTE(A1,".","x",3))-1)&".0"
0
 
LVL 25

Expert Comment

by:Brian B
ID: 36536819
Unfortunately, the other solutions won't adapt to the number of digits in the individual octets changing.
0
 

Author Closing Comment

by:rdefino
ID: 36536865
Tbone2k,

Worked like a charm!

thanks
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36538193
>Unfortunately, the other solutions won't adapt to the number of digits in the individual octets changing

That's partly true of mine.....

My solution works as long as there are 3 digits after the last .

The question did say "remove the last 3 digits and replace it with a 0"

Was the 3 variable then?

barry
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 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