Convert IP address to Decimal

**zipzip** · 04-12-2010, 02:02 PM

Greetings,

I have a bulk of IP addresses that I need to convert to decimal, and using websites that provide such calculators would be cumbersome.

Since I was unable to find any EXCEL function that does this, I tried using several built-in functions to
- split one cell of an IP address into four cells
- converting those four cells into binary (using DEC2BIN)
- merging those four cells into a 32-bit binary

Now, all I need is a function to convert 32-bit binary to decimal. Problem is, the function BIN2DEC only converts up to 10-bit.

I am quite unfamiliar with EXCEL and any related programming. Can you provide some guidance, or some hints to accomplish this?

Thanks in advance,
Zippie

**shg** · 04-12-2010, 02:06 PM

Welcome to the forum.

If A1:D1 contains the elements of an IP address, e..g., 192 168 1 1, then

=SUMPRODUCT(A1:D1, 256^{3,2,1,0}) returns 3232235777, which is hex C0A80101

**zipzip** · 04-12-2010, 03:06 PM

You are totally AWESOME!!!!!!!

**XLPadawan** · 08-19-2014, 04:54 PM

Zippie,
I have a spreadsheet that converts 32-bit binary to decimal. I'm new to this forum and I see no way to upload my spreadsheet for you. Do you know how I can get it to you?