Environment
All
Cause
Formatting of IP address turns IP into a signed integer
Symptoms
IP addresses show up as negative numbers
Resolution
- For each column you wish to convert, insert two columns to the right
- In the first new column (immediately to the right of the column you are trying to convert), insert the following formula:
=DECIMAL(RIGHT(DEC2HEX(T2),8),16)
- where 'T2' is the reference cell of the cell you are trying to convert
- This converts the signed integer to an unsigned integer. I broke it out from the below formula for easier readability, though you can technically do it all in one formula (see Resolution 2 below).
- In the second new column (immediately to the right of the column you put above formula in), insert the following formula:
=INT(U2/256^3)&"."&INT((U2-(INT(U2/256^3))*256^3)/256^2)&"."&INT((U2-((INT(U2/256^3))*256^3)-(INT((U2-(INT(U2/256^3))*256^3)/256^2)*256^2))/256)&"."&INT((U2-((INT(U2/256^3))*256^3)-((INT((U2-(INT(U2/256^3))*256^3)/256^2))*256^2))-((INT((U2-((INT(U2/256^3))*256^3)-(INT((U2-(INT(U2/256^3))*256^3)/256^2)*256^2))/256))*256))
- where 'U2' is the reference cell of the new cell just to the right of the original column
- This takes the newly converted unsigned integer and converts it back to original IP (the value you should be expecting).
- The formula can be copied down through the rest of the document after verifying it works correctly on the first cell(s)
Result with two new columns:
Resolution 2
Using one formula / column
- For each column you wish to convert, insert one column to the right
- In the new column, insert following formula:
=INT(DECIMAL(RIGHT(DEC2HEX(T2),8),16)/256^3)&"."&INT((DECIMAL(RIGHT(DEC2HEX(T2),8),16)-(INT(DECIMAL(RIGHT(DEC2HEX(T2),8),16)/256^3))*256^3)/256^2)&"."&INT((DECIMAL(RIGHT(DEC2HEX(T2),8),16)-((INT(DECIMAL(RIGHT(DEC2HEX(T2),8),16)/256^3))*256^3)-(INT((DECIMAL(RIGHT(DEC2HEX(T2),8),16)-(INT(DECIMAL(RIGHT(DEC2HEX(T2),8),16)/256^3))*256^3)/256^2)*256^2))/256)&"."&INT((DECIMAL(RIGHT(DEC2HEX(T2),8),16)-((INT(DECIMAL(RIGHT(DEC2HEX(T2),8),16)/256^3))*256^3)-((INT((DECIMAL(RIGHT(DEC2HEX(T2),8),16)-(INT(DECIMAL(RIGHT(DEC2HEX(T2),8),16)/256^3))*256^3)/256^2))*256^2))-((INT((DECIMAL(RIGHT(DEC2HEX(T2),8),16)-((INT(DECIMAL(RIGHT(DEC2HEX(T2),8),16)/256^3))*256^3)-(INT((DECIMAL(RIGHT(DEC2HEX(T2),8),16)-(INT(DECIMAL(RIGHT(DEC2HEX(T2),8),16)/256^3))*256^3)/256^2)*256^2))/256))*256))
- Where 'T2' is the reference cell of the cell you are trying to convert
- The formula can be copied down through the rest of the document after verifying it works correctly on the first cell(s)
Notes
As noted in this article, the process search will convert the IP you are searching for to a signed integer (negative number). This can be particularly confusing on CSV exports, where columns like comms_ip and interface_ip will also show this negative number. The above Excel formulas can be utilized as a work around to convert the number back into an IP address, in case it proves useful to anyone looking at or reporting from those exports.
Related Content
ipaddr query conversion - A small community Q/A on the subject with more detail
CB-15266 -> Tracking number which can be perused on each version's release notes to follow if an update on this issue has been introduced.
