How to Extract IP Addresses from Text and Log Files (Online, Excel, grep)

· 5 minutes read

How to Extract IP Addresses from Text and Log Files (Online, Excel, grep)

Server logs, firewall reports, mail headers and monitoring alerts all have one thing in common: the IP addresses you care about are buried between timestamps, ports, process IDs and version numbers. Whether you want to block an attacker, whitelist a partner or just count unique visitors, the first step is the same – extract the IP addresses from the text into a clean list.

This guide shows the quickest way to do it and how to do the same job in Google Sheets, Excel and on the command line.

The fastest way: an online IP address extractor

  1. Open the IP address extractor.
  2. Paste your text, open a file or drop a .log file onto the input box.
  3. The IP addresses appear on the right immediately – by default each one only once.
  4. Pick the options you need:
    • Find – IPv4 only, IPv6 only, or both.
    • Unique only – turn it off if you want every occurrence (useful for counting).
    • Sort numerically – 10.0.0.9 goes before 10.0.0.10, which a plain A–Z sort gets wrong.
    • Keep ports – keep 10.0.0.12:8080 as it is, or reduce it to 10.0.0.12.
    • Separate with – new lines, commas or spaces, ready for a firewall rule.
  5. Copy or download the result.

The tool validates every address: each IPv4 octet must be between 0 and 255, so 999.1.1.1 is ignored, and so are version numbers like 2.4.1 or 1.2.3.4.5. IPv6 addresses are checked against the full rules, which means times such as 12:30:45 and MAC addresses are not picked up. Everything runs in your browser, so internal addresses never leave your computer.

How to extract IP addresses in Google Sheets

If the text is already in a spreadsheet, REGEXEXTRACT returns the first IPv4 address from a cell:

=REGEXEXTRACT(A2, "\b(?:\d{1,3}\.){3}\d{1,3}\b")

Fill it down the column and wrap it in IFERROR(…, "") for cells without an address. To get a unique, sorted list afterwards, use:

=SORT(UNIQUE(FILTER(B2:B, B2:B<>"")))

Two limitations: REGEXEXTRACT returns only one match per cell, and the pattern above accepts invalid values such as 999.999.999.999. If a cell may contain several addresses, paste the column into the online tool instead.

How to extract IP addresses in Excel

Recent Microsoft 365 builds include REGEXEXTRACT as well. With the third argument set to 1 it returns all matches, spilled into neighbouring cells:

=REGEXEXTRACT(A2, "\b(?:\d{1,3}\.){3}\d{1,3}\b", 1)

Older Excel versions have no regular expressions. There you either need a VBA function or Data → Text to Columns with a space delimiter followed by manual filtering – slow and error-prone for large logs.

How to extract IP addresses with grep (Linux and macOS)

To list unique IPv4 addresses from a log file:

grep -oE '\b([0-9]{1,3}\.){3}[0-9]{1,3}\b' access.log | sort -u

To sort them numerically instead of alphabetically, sort each octet as a number:

grep -oE '\b([0-9]{1,3}\.){3}[0-9]{1,3}\b' access.log | sort -u -t . -k1,1n -k2,2n -k3,3n -k4,4n

And to find the noisiest clients, count the occurrences:

grep -oE '\b([0-9]{1,3}\.){3}[0-9]{1,3}\b' access.log | sort | uniq -c | sort -rn | head

If you need strict validation of the 0–255 range, use this longer pattern with grep -oP or in any regex engine:

\b(?:(?:25[0-5]|2[0-4]\d|1?\d?\d)\.){3}(?:25[0-5]|2[0-4]\d|1?\d?\d)\b

How to extract IP addresses with PowerShell (Windows)

Select-String -Path .\access.log -Pattern '\b(?:\d{1,3}\.){3}\d{1,3}\b' -AllMatches |
  ForEach-Object { $_.Matches.Value } |
  Sort-Object -Unique

Sort-Object sorts the values as text. For a numeric sort, cast them: Sort-Object { [version]$_ } -Unique.

Common pitfalls

  • Version numbers and other dotted numbers. A simple pattern happily matches 1.2.3.4 inside v1.2.3.4.5. Make sure the match is not preceded or followed by another dot and digit.
  • Invalid octets. \d{1,3} allows 300. Validate the range or use a tool that does.
  • Ports. 10.0.0.5:8080 and 10.0.0.5:9090 are the same host. Strip ports before removing duplicates unless the port matters to you.
  • Alphabetical sorting. Plain sorting puts 10.0.0.10 before 10.0.0.9. Sort by each octet numerically.
  • IPv6 forgotten. Many scripts only look for IPv4. Modern logs often contain addresses like 2001:db8::1 or [2001:db8::1]:443, and IPv4-mapped forms like ::ffff:192.0.2.1.
  • Leading zeros. 010.001.002.003 is ambiguous – some software reads the parts as octal numbers. It is safer to treat such values as invalid and look at them manually.

FAQ

Can I extract both IPv4 and IPv6 addresses at once?

Yes. The IP address extractor finds both by default and shows a separate count for each. Use the Find option to limit the result to one version.

How do I remove duplicate IP addresses from a list I already have?

Paste the list into the extractor with Unique only on, or use the duplicate remover, which works on any line-based list.

How do I find which IPs from one list are missing in another?

Use compare two lists: paste, for example, all addresses seen in the log into List A and your whitelist into List B, then pick Only in A.

Is it safe to paste production logs into an online tool?

With this tool, yes – the extraction is done locally by JavaScript in your browser and nothing is sent to a server. As a rule, check this for any online tool before pasting sensitive data.