Function to add multiple "."s with a cell?
 

Hi All,

I assume there is a quick and easy formula for this, but the only way I
have figured out how to do it requires three forumlas in 3 seperate
cells.

Basically, I need to turn a 8 digit number into an IP address.

So, Say in cell A1 I have 19216801. I need a formula that will make
cell A2 read 192.168.0.1

I assume that theres an easy way to do this, but I've yet to figure it
out.

Your help will be much appreciated!


--
CDiddy
------------------------------------------------------------------------
CDiddy's Profile: http://www.excelforum.com/member.php...o&userid=29492
View this thread: http://www.excelforum.com/showthread...hreadid=491925

Function to add multiple "."s with a cell?
 

Two ways...

You could leave it as is but use a custom format. Right click on the
cell, choose Format Cells, pick Custom from the list and enter
###"."###"."#"."# in the Type box.

Or use a formula in another cell such as

=LEFT(A1,3)&"."&MID(A1,4,3)&"."&MID(A1,7,1)&"."&RI GHT(A1,1)

- John
www.JohnMichl.com

Function to add multiple "."s with a cell?
 

What happens when 19216801represents 19.216.80.1, or there are more than 8
digits?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"CDiddy" wrote in
message ...

Hi All,

I assume there is a quick and easy formula for this, but the only way I
have figured out how to do it requires three forumlas in 3 seperate
cells.

Basically, I need to turn a 8 digit number into an IP address.

So, Say in cell A1 I have 19216801. I need a formula that will make
cell A2 read 192.168.0.1

I assume that theres an easy way to do this, but I've yet to figure it
out.

Your help will be much appreciated!


--
CDiddy
------------------------------------------------------------------------
CDiddy's Profile:
http://www.excelforum.com/member.php...o&userid=29492
View this thread: http://www.excelforum.com/showthread...hreadid=491925

Function to add multiple "."s with a cell?
 

This is exactly what I needed. Thanks very much!

I knew about Left/Right functions, but not the MID!

C

Two ways...

You could leave it as is but use a custom format. Right click on the
cell, choose Format Cells, pick Custom from the list and enter
###"."###"."#"."# in the Type box.

Or use a formula in another cell such as

=LEFT(A1,3)&"."&MID(A1,4,3)&"."&MID(A1,7,1)&"."&RI GHT(A1,1)

- John


--
CDiddy
------------------------------------------------------------------------
CDiddy's Profile: http://www.excelforum.com/member.php...o&userid=29492
View this thread: http://www.excelforum.com/showthread...hreadid=491925

Function to add multiple "."s with a cell?
 

I wondered the same thing when I first posted but the original poster
asked specifically for an 8 character solution.

I don't know much about IP address standards but I believe that some
have two characters instead of one in those rightmost sections. If
there is a different number of characters or if the pattern is
different than the original poster suggested, I would think that you'd
be able to determine by your companies IP naming standards how to
modify the format or the parsing formula.

It begs the question is 123.123.1.1 the same as 123.123.01.01? If so,
I'd think you want to always use the leading zeros so that the formula
is consistent. On the otherhand, CDiddy may have had a one time need
and may not be concerned about future uses or IPs that look different
than the first example.

- John
www.JohnMichl.com

Function to add multiple "."s with a cell?
 

Any part of the IP address can be 1 to 3 digits.

It seems that the last statement is true John as the OP seems happy.

Regards

Bob

"John Michl" wrote in message
ups.com...
I wondered the same thing when I first posted but the original poster
asked specifically for an 8 character solution.

I don't know much about IP address standards but I believe that some
have two characters instead of one in those rightmost sections. If
there is a different number of characters or if the pattern is
different than the original poster suggested, I would think that you'd
be able to determine by your companies IP naming standards how to
modify the format or the parsing formula.

It begs the question is 123.123.1.1 the same as 123.123.01.01? If so,
I'd think you want to always use the leading zeros so that the formula
is consistent. On the otherhand, CDiddy may have had a one time need
and may not be concerned about future uses or IPs that look different
than the first example.

- John
www.JohnMichl.com