#### tl;dr

Convert IPv4 dotted decimal in cell `A2`

to decimal integer:

```
=SUM(MAP(TEXTSPLIT(A2,"."),SEQUENCE(,4),LAMBDA(o,i,o*256^(4-i))))
```

Convert IPv4 dotted decimals in column with range `A2:A11`

to decimal integers:

```
=MAP(CHOOSECOLS(A2:A11,1),LAMBDA(ip,SUM(MAP(TEXTSPLIT(ip,"."),SEQUENCE(,4),LAMBDA(o,i,o*256^(4-i))))))
```

Padding the IP addresses with `0`

s is an incorrect approach that produces the right output in this case (see: **wrong**, and **luck**), and sorting over each octet independently is unnecessarily complicated.

The dotted decimal notation already corresponds to the natural numbers with the same intuitive order you are wanting. We just need a way to convert natural numbers between this notation and the familiar base-10 decimal notation Excel understands.

IPv4 addresses in dotted decimal notation have the form `A.B.C.D`

, but, the periods `.`

are only used to enhance readability, so let's write it as `ABCD`

. Each of the 4 letters represents a single digit with 256 (0-255) possible values — i.e., a 4-digit number in base-256.

Thus, our goal is to convert notation of natural numbers from base-256 to base-10.

Recall the formula for converting any number `n`

(base-`N`

) to `t`

(base-10) is simply the sum of each digit multiplied by its place value (digits in `n`

are separated with a period `.`

):

`n` |
Base (`N` ) |
Formula |
`t` |

`4.2.0` |
`10` |
`4*10² + 2*10¹ + 0*10⁰` |
`420` |

`1.1.0.1` |
`2` |
`1*2³ + 1*2² + 0*2¹ + 1*2⁰` |
`13` |

`127.0.0.1` |
`256` |
`127*256³ + 0*256² + 0*256¹ + 1*256⁰` |
`2130706433` |

This formula can easily be expressed in Excel, assuming your IPv4 dotted decimal address is in cell `A2`

:

```
=SUM(MAP(TEXTSPLIT(A2,"."),SEQUENCE(,4),LAMBDA(o,i,o*256^(4-i))))
```

Instead of auto-generating the formula using the click-and-drag technique, you can also apply this formula across a cell range with something like the following (using `A2:A11`

as a column of dotted decimal IPv4 addresses):

```
=MAP(CHOOSECOLS(A2:A11,1),LAMBDA(ip,SUM(MAP(TEXTSPLIT(ip,"."),SEQUENCE(,4),LAMBDA(o,i,o*256^(4-i))))))
```