Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

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

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
rdefino
Asked:
rdefino
1 Solution
 
barry houdiniCommented:
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
 
Brian BIndependant Technology ProfessionalCommented:
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
 
CluskittCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Brian BIndependant Technology ProfessionalCommented:
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
 
Brian BIndependant Technology ProfessionalCommented:
Unfortunately, the other solutions won't adapt to the number of digits in the individual octets changing.
0
 
rdefinoAuthor Commented:
Tbone2k,

Worked like a charm!

thanks
0
 
barry houdiniCommented:
>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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now