Convert text string to IP address

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I've got a bunch of text strings similar to this one: 064190119066. Is there
a way to convert this into an IP address in Excel?

Thanks!
 
Hi

Try the formula below, If your data is in A1
=LEFT(A1,3) &"."& MID(A1,4,3 ) &"."& MID(A1,7,3) &"."& RIGHT(A1,3)
 
Mike said:
I've got a bunch of text strings similar to this one: 064190119066.
Is there a way to convert this into an IP address in Excel?
Click to expand...

An IP(v4) address is usually presented in "dot notation", composed of 4
parts, each representing one octet. The representation of each octet may or
may not have leading zeros.

So I presume the above is 64.190.119.66. This can be constructed one of two
ways, depending on your preference:

=left(A1,3) & "." & mid(A1,4,3) & "." & mid(A1,7,3) & "." & right(A1,3)

=--left(A1,3) & "." & --mid(A1,4,3) & "." & --mid(A1,7,3) & "."
& --right(A1,3)

The first form yields 064.190.119.066 . The second form yields
64.190.119.66 . Both forms are equally acceptable.

This presumes that you entered the original 12-character string correctly,
either prefixed by an apostrophe (') or in a cell pre-formatted as Text in
order to preserve the leading zero(s).
 
I've got a bunch of text strings similar to this one: 064190119066. Is there
a way to convert this into an IP address in Excel?

Thanks!
Click to expand...

Here's one way:

=TEXT(A1,"000\.000\.000\.000")

--ron
 
Back
Top