8

I have a list of IP addresses in the following form:

    010.125.015.013
    010.125.153.012
    010.125.012.135

I'm unable to execute ping with that form as windows thinks it's dotted octal form. I need to remove leading 0's after each dot.

It's not that simple as some of them have no leading 0's after dots at all and some of them have it only on last octet where some have it on third octet or both.

Unless there's a solution to force ping to interpret is as a dotted decimal IP address.

Can anyone help?

Thanks

  • 2
    Shall we assume that you want to preserve a 0 in something like 123.234.000.276? (Yes, bogus example.) – a CVn Jun 23 '15 at 20:08
  • there has to be no 0's so in my example: '010.125.015.013' '010.125.153.012' '010.125.012.135' They need to be: '10.125.15.13' '10.125.153.12' '10.125.12.135' But when it's for example: '010.155.105.013' It has to be: '10.155.105.13' – Michal Jun 23 '15 at 20:10
  • You can do this quite easily with the power of regex! Use this link to get started. The regex is as simple as ^0 (match a zero at the beginning of the string!) – Dudemanword Jun 23 '15 at 22:21
  • 2
    Removing leading zeros changes the IP, since the leading zero signifies octal numbers. – CodesInChaos Jun 24 '15 at 7:00
  • 1
    @CodesInChaos In general yes. But the input looks like somebody simply prepended zeros to make each octet three digits long. It seems unlikely that somebody would also change the notation to be octal while performing that operation, besides it wouldn't work for octets in the range 64 to 99. I am guessing the task is to convert IPs from a non-standard notation with decimal numbers with leading zeros to a standard notation with decimal numbers without leading zeros. – kasperd Jun 24 '15 at 10:37
11

Here's what you can do:

  1. I assume each IP is in one single cell as text. Now convert this single cell to columns (by using the Text-to-Columns feature) specifying . (dot) as delimiter. They shall now get distributed as numbers in next 4 consecutive cells, leading zero should now be gone

  2. Use function Concatenate (see Excel help for details and join the 4 numbers by including . after each string. Now you should have a cell with same IP excluding all leading zeros.

12

What about

=INT(MID(A1,1,3)) & "." & 
 INT(MID(A1,5,3)) & "." & 
 INT(MID(A1,9,3)) & "." & 
 INT(MID(A1,13,3))
6

This one uses SUBSTITUTE:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("@."&TRIM(C5),".0","."),".0","."),"@.","")

enter image description here

First, it trims the blank space around the IP (in C5), adds a prefix @., substitutes .0 with . twice and then removes added prefix @..

  • Whew! I find this answer so "lateral thinking" compared to the other ones so far :) +1 because (kidding) you win at codegolf (more seriously) the use of TRIM to make sure not leaving any eventual white space (...and still winning codegolf so far! ;-D) – danicotra Jun 24 '15 at 0:03
3

Beaten on time (I must translate formulas from Italian to English... also I only dispose of LibreOffice...) Anyway, supposing IP addresses are on column A, I'd suggest this:

=CONCATENATE(VALUE(MID(A1,1,3)),".",VALUE(MID(A1,5,3)),".",VALUE(MID(A1,9,3)),".",VALUE(MID(A1,13,3)))
  • @fheub: thank you so much for the appreciation. The Italian version of Ms-Excel has almost all different formulas' names; nonetheless, we use ; not , as formulas' parameters separator so it takes to change them - btw in Italy we use comma (not dot) as the separator for the decimal part for numbers. Last but not least, I use Excel at work but at home I only have LibreOffice and must admit that it's not as well documented as Excel (I always hope formula names remain the same between the two) and definitely lacks some nice features compared to it... – danicotra Jun 24 '15 at 17:50
  • P.S. I was forgetting... LibreOffice differs from Excel even in the fact that sheets/cells reference are made with . in LibreOffice whereas in Excel you must use ! instead ('Sheet 1'.A1 <=> 'Sheet 1'!A1). – danicotra Jun 24 '15 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.