Cisco Support Community
Community Member

CDR origIpAddr to a readable format in excel

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:

From this doc

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


Re: CDR origIpAddr to a readable format in excel

i would start here

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

Community Member

Re: CDR origIpAddr to a readable format in excel

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

Re: CDR origIpAddr to a readable format in excel


I've previously (on Windows CCM) used this SQL function called from my queries to extract the IPs (taken from:

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


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.



Please rate helpful posts...

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
Community Member

Re: CDR origIpAddr to a readable format in excel

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)

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
    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
        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

Public Function GetNormalizedIPAddress( _
      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 ->
'  255.10 ->
'  FFFE0001 ->
'  258.-1.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, ".")
      If ZeroPadOctets > 0 Then
         For Index = 0 To 3
            If Index + 1 <= ZeroPadOctets Then Nodes(Index) = Right("00" & Nodes(Index), 3)
         Next Index
         For Index = 0 To 3
            If 4 - Index <= -ZeroPadOctets Then Nodes(Index) = Right("00" & Nodes(Index), 3)
         Next Index
      End If
      GetNormalizedIPAddress = Join(Nodes, ".")
   End If

End Function

And its done.

Best Regards

Ing. Allan O. Mancera


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

CDR origIpAddr to a readable format in excel

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.


That should give you what youre looking for.


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


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))

CreatePlease to create content