Custom Cell Format Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Me - 24 Sep 2007 22:10 GMT

How would I format a cell in Excel 2003 that will show any IP address as four
groups of three numbers separated by periods?

For example, I would want 123.98.0.876 to appear as 123.098.000.876. The
most extreme example being that I would want 1.1.1.1 to show as
001.001.001.001.

I looked in the customer formatting box in Excel, but none of the formatting
options allowed what I need.

Thanks in advance.

Reply to this Message

Dave Peterson - 24 Sep 2007 22:57 GMT

Formatting won't help.

You could put each of the numbers in a separate cell (say B1:E1) and put a
formula like this in A1:
=text(b1,"000.")&text(c1,"000.")&text(d1,"000.")&text(e1,"000")

> How would I format a cell in Excel 2003 that will show any IP address as four
> groups of three numbers separated by periods?
[quoted text clipped - 7 lines]
>
> Thanks in advance.

Signature

Dave Peterson

Reply to this Message

David McRitchie - 28 Sep 2007 02:30 GMT

Several solutions at
Sorting TCP/IP Addresses, and the like
http://www.mvps.org/dmcritchie/excel/sorttcp.htm
and this type of numbering also applies to indexing in an outline
numbering structure (Table of Contents).

Signature

HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

> Formatting won't help.
>
[quoted text clipped - 13 lines]
>>
>> Thanks in advance.

Reply to this Message

Rate this thread: