cancel
Community Member

Hi Guys,

Has anyone found a formula in excel to convert the origIpAddr in the CRD files in to a readable ip address.

I have found this

Step 1 Convert the database display (-1139627840) to a hex value.

The hex value equals 0xBC12A8C0.

Step 2 Reverse the order of the hex bytes, as shown below:

CO A8 12 BC

Step 3 Convert the four bytes from hex to decimal, as shown below:

192 168 18 188

Step 4 The IP address displays in the dotted decimal format:

192.168.18.188

But am hoping someone has created a formula to get around this.

8 REPLIES
VIP Red

i would start here

http://office.microsoft.com/en-gb/excel/HP052091111033.aspx

Please remember to rate useful posts, by clicking on the stars below.

Community Member

Hello guys:

Any update on how to transform the field in a IP Address?

any update will be greatly appreciated.

Best regards

Ing. Allan O. Mancera

Super Bronze

Hi

I've previously (on Windows CCM) used this SQL function called from my queries to extract the IPs (taken from:http://markmail.org/message/eyiwyjj6zofyjlm5):

`CREATE function dbo.decimalToIP(@ip INT) RETURNS VARCHAR(15) AS BEGIN DECLARE @binip BINARY(4) SET @binip = CONVERT(BINARY(4), at ip) DECLARE @charip varchar(8) SET @charip = CONVERT(varchar(8), at binip) DECLARE @1 int, @2 int, @3 int, @4 int SET @4 = ASCII(SUBSTRING(@charip,1,1)) SET @3 = ASCII(SUBSTRING(@charip,2,1)) SET @2 = ASCII(SUBSTRING(@charip,3,1)) SET @1 = ASCII(SUBSTRING(@charip,4,1)) DECLARE @output varchar(15) SET @output = CONVERT(varchar, at 1) + '.' + CONVERT(varchar, at 2) + '.' + CONVERT(varchar, at 3) + '.' + CONVERT(varchar, at 4) RETURN @output END https://10.65.0.4/cuadmin`

If you are talking linux then you may be better doing it in Excel, or you may have to work some magic to get it working on Informix.

Regards

Aaron

Community Member

Hey guys:

Working with Excel Macros in VBA I finally solved the issue with the combination of the next functions, hope this helps

Using the DEC2HEX command we obtain an HEX number which we have to reverse, so I used the next function to reverse the string:

Function ReverseText(rt As Range)

Application.Volatile
Dim iLength As Integer
Dim iCount As Integer
Dim ipCount As Integer
Dim LValue  As String

iLength = Len(rt)
ReverseText = ""

If iLength = 7 Then     ' Validate if we have an Hex number in the type 0xXXXXXXX
LValue = "0" & rt.Value     ' Concatenate a 0 to have an 8 digit Hex number
iLength = Len(LValue)     ' Recalculate the length to have the 8 Hex digit number
Else
LValue = rt.Value
End If

For iCount = iLength To 1 Step -2
If ipCount < 4 Then
ReverseText = ReverseText & _
Mid(LValue, iCount - 1, 2)
ipCount = ipCount + 1
Else
iCount = 1
End If
Next iCount

End Function

After we have the reversed HEX number, I used this next function to normalize the IP address into an standard IPv4 text:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range

Application.EnableEvents = False
For Each Cell In Target
If Not Intersect(Target, [IPAddresses]) Is Nothing Then
If Len(Cell) > 0 Then Cell = GetNormalizedIPAddress(Cell)
End If
Next Cell
Application.EnableEvents = True

End Sub

ByVal Text As String, _
Optional ByVal ZeroPadOctets As Long = 0 _
) As String

' Convert text to IP address. Text can be any value or an eight character
' hexidecimal number. Examples:
'
'  0 -> 0.0.0.0
'  255.10 -> 255.10.0.0
'  FFFE0001 -> 255.254.0.1
'  258.-1.0.0 -> 255.0.0.0
'
' Use the parameter ZeroPadOctets to pad each octet with zeroes. Pass a
' positive integer from 1 to 4 to pad that number of octets starting from
' the left. Pass a negative integer from -1 to -4 to pad that number of
' octets starting from the right.

Dim Nodes As Variant
Dim Index As Long
Dim Result As String

If Len(Text) > 0 Then
Nodes = Split(Text, ".")
If UBound(Nodes) = 0 And Len(Nodes(0)) = 8 Then
ReDim Nodes(0 To 3)
For Index = 0 To 3
Nodes(Index) = CStr(CLng("&H" & Mid(Text, Index * 2 + 1, 2)))
Next Index
End If
For Index = 0 To UBound(Nodes)
If Not IsNumeric(Nodes(Index)) Then Nodes(Index) = 0
Nodes(Index) = Application.Max(0, Application.Min(255, Nodes(Index)))
Next Index
Result = Join(Nodes, ".") & Left(".0.0.0", 6 - UBound(Nodes) * 2)
Nodes = Split(Result, ".")
For Index = 0 To 3
If Index + 1 <= ZeroPadOctets Then Nodes(Index) = Right("00" & Nodes(Index), 3)
Next Index
Else
For Index = 0 To 3
If 4 - Index <= -ZeroPadOctets Then Nodes(Index) = Right("00" & Nodes(Index), 3)
Next Index
End If
End If

End Function

And its done.

Best Regards

Ing. Allan O. Mancera

CCVP

Community Member

## If you need to use this

If you need to use this formula in French, you just have to replace HEX2DEC to HEXDEC , DEC2HEX to DECHEX and MID to STXT

Community Member

If you have the comma delimited CDR file open it in excel. Take the decimal value of origIPAddr and use the following formula to convert it to an IP address.

=CONCATENATE(HEX2DEC(MID(DEC2HEX(A1+4294967296),8,2)),".",HEX2DEC(MID(DEC2HEX(A1+4294967296),6,2)),".",HEX2DEC(MID(DEC2HEX(A1+4294967296),4,2)),".",HEX2DEC(MID(DEC2HEX(A1+4294967296),2,2)))

That should give you what youre looking for.

Regards,

Dan Schmitt

Community Member

## Here's another handly formua

Here's another handly formua to convert CDR fields. This one converts a dateTime field into something readable. Hopefully someone finds it useful. Tim

=(A1)/86400+DATE(1970,1,1)

Community Member

## A simpler version of a

A simpler version of a CONCATENATE method in Excel is:

=CONCATENATE(BITAND(H2, 255), ".", BITAND(ROUNDDOWN(H2 / 256, 0), 255), ".", BITAND(ROUNDDOWN(H2 / 256^2, 0), 255), ".", BITAND(ROUNDDOWN(H2 / 256^3, 0), 255))

3333
Views
30