| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help: I need a function for finding next cell vertically with value | Excel Worksheet Functions | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
| Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
| Hiding a function value in a cell until function used | Excel Worksheet Functions | |||
| Function making cell really "empty" | Excel Worksheet Functions | |||
Linear Mode
