Excel, remove leading 0's from IP address

Solution 1:

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.

Solution 2:

What about

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

Solution 3:

This one uses SUBSTITUTE:

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

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