We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Convert IP Address to DWORD

5 - Atom

I work with IP addresses every day and Alteryx doesn't have built-in functions to handle IP addresses. However, it has every function you can imagine to work with Integers.

The Marquee Crew gave us this lifesaver a couple of years ago: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Use-RegEx-and-build-a-Macro-to-Vali...

This macro lets you validate than an IP address is a valid IP, whether it is a public or private IP address, and can also sort them.


I had a use case that fell outside of this.

I needed to determine if an IP address was part of a range and, if it was, create two new ranges on either side of it.

For example:

  • Given an IP range: to
  • Is this IP is in the range:

The dotted decimal notation we use for IP addresses is just a user-friendly way of representing a 32-bit binary number.

Using the IntToBin() function and some string concatenation, you can convert an IP to its binary equivalent. You have to PadLeft with 0 to make sure each section is a full 8 bits.

IP AddressBinary Notation











The you just concatenate them all together to get a 32-bit binary number.





PadLeft(([Octet 1 Binary] + [Octet 2 Binary] + [Octet 3 Binary] + [Octet 4 Binary]),32,'0')






The next step is using BinToInt() to convert the binary to an integer.

IP AddressBinary NotationInteger Notation

Wait a second. Why do we have negative Integers?

Standard Binary math uses a leading 1 as the indicator for negative numbers. I can't just add a leading 0 because then it wouldn't be a 32-bit Integer, it'd be a 33-bit Integer.

A 32-bit integer with a +/- is just an integer, but an unsigned, 32-bit integer is called a DWORD.

The fix is to add 2^32 (4,294,967,296) to the negative Integer which gives this chart.

IP AddressBinary NotationInteger NotationDWORD Notation

Okay. Now what?

Let's use the IP's I mentioned at the beginning of this post and throw in for good measure.

IP AddressDWORD Notation

The first two are the range and the second two are the test candidates.

Let's label them.

LabelIP AddressDWORD Notation

Now you can have a Boolean called IsInRange and have





IF [Test#] >= RangeStart and [Test#] <= RangeEnd THEN 1 ELSE 0 ENDIF





Test1 would be TRUE and Test2 would be FALSE.


I have another Macro to convert Integer back to IP address dotted decimal notation.


The actual work I do with these IPs as Integers is too complex to include here and still a work in progress. I am trying to use these number to build new ranges that exclude specific IP addresses or ranges of IP addresses. I can easily add or subtract 1 from an Integer and convert that back to IP address using these macros.


* How to determine if an IP address is valid - https://www.oreilly.com/library/view/regular-expressions-cookbook/9780596802837/ch07s16.html