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!

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)&"."&RIGHT(A1,1)

- John
www.JohnMichl.com

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" <CDiddy.1zqi8o_1134067204.0246@excelforum-nospam.com> wrote in
message news:CDiddy.1zqi8o_1134067204.0246@excelforum-nospam.com...
>
> 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
>

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

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" <waxwing1@gmail.com> wrote in message
news:1134077518.211068.36110@g47g2000cwa.googlegroups.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
>

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