[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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