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

5
  • 2
    Shall we assume that you want to preserve a 0 in something like 123.234.000.276? (Yes, bogus example.)
    – user
    Jun 23, 2015 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, 2015 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!) Jun 23, 2015 at 22:21
  • 2
    Removing leading zeros changes the IP, since the leading zero signifies octal numbers. Jun 24, 2015 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, 2015 at 10:37

4 Answers 4

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.

0
12

What about

=INT(MID(A1,1,3)) & "." & 
 INT(MID(A1,5,3)) & "." & 
 INT(MID(A1,9,3)) & "." & 
 INT(MID(A1,13,3))
0
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 @..

1
  • 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, 2015 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)))
2
  • @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, 2015 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, 2015 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.