| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text string to IP address
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert text string to IP address
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) -- Best regards Rajesh Mehmi "Mike" wrote in message ... 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert text string to IP address
"Mike" wrote:
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? 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). |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert text string to IP address
On Mon, 21 Sep 2009 15:53:01 -0700, Mike
wrote: 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! Here's one way: =TEXT(A1,"000\.000\.000\.000") --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| cannot convert text string into value | Excel Worksheet Functions | |||
| Convert numbers to text string | Excel Discussion (Misc queries) | |||
| return cell address of longest text string in a range | Excel Discussion (Misc queries) | |||
| Convert text string to date | Excel Worksheet Functions | |||
| How can I convert text to reference ? Ex: ISNUMBER(ADDRESS(6;10;2. | Excel Discussion (Misc queries) | |||
Hybrid Mode
