Data Import & Export

How to Remove MAC Addresses and Keep IP Addresses in Excel

John Wilson Oct 15, 2026 869 views

Question details

The user needs to clean a column containing both MAC addresses and IP addresses by removing the MAC addresses in bulk without manually editing each cell.

How to Remove MAC Addresses from an Excel Column and Keep IP Addresses

Product: Excel
Device & OS: not provided
Scenario: Filtering and cleaning network data in a spreadsheet where single cells either contain one type of address or a mixture of both MAC and IP addresses.
Observed behavior: The goal state is to effectively isolate and retain only the IP addresses in the dataset by utilizing bulk data separation techniques.

Before you start

Before proceeding, determine if your cells contain only one address per cell or if the MAC and IP addresses share the same cell, as this will dictate whether you should use a formula or Power Query.

Solution 1Recommended

Use a Helper Formula for Single-Address Cells

Use this method if each cell in your column contains either a MAC address or an IP address, but not both. It identifies IP addresses by searching for periods.

IPv4 addresses are uniquely identifiable by their periods (.), whereas MAC addresses typically use colons or hyphens. We can use Excel's SEARCH function to find the period and extract only the IP addresses.

Insert a helper column

Click the column header next to your address data (e.g., Column B if your data is in Column A), right-click, and select 'Insert' to create a new blank column.

Apply the filtering formula

In the first blank cell of the new column (e.g., B2), type the following formula: =IF(ISNUMBER(SEARCH(".",A2)),A2,""). Press Enter.

Fill the formula down

Select cell B2, click and hold the small square at the bottom-right corner of the cell (the fill handle), and drag it down to the end of your dataset to apply the formula.

Convert formulas to values

Highlight the entire helper column, press Ctrl+C to copy, right-click the first cell of the column, and select 'Paste as Values' (the clipboard icon with a 123) to lock in the IP addresses and remove the underlying formula.

Use a Helper Formula for Single-Address Cells

Result: Your new column will now display only the IPv4 addresses, while the cells that originally contained MAC addresses will appear completely blank.

Solution 2

Use Power Query for Mixed Text Cells

If a single cell contains both a MAC address and an IP address separated by a delimiter (like a space or comma), Power Query is the best tool to split and filter the data.

Efficient Data Cleaning

Extract and Clean Network Data Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful text functions and data processing tools that operate exactly like Excel. You can quickly filter, split, and extract IP addresses using familiar formulas or the built-in Text to Columns feature to manage your network datasets effortlessly.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the workbook containing your mixed MAC and IP address data.
2. Enter the Extraction Formula: In an adjacent blank column, type `=IF(ISNUMBER(SEARCH(".",A2)),A2,"")` to identify the IP addresses.
3. Copy and Paste as Values: Drag the formula down to apply it to all rows, then copy the results and paste them as values to safely remove the MAC addresses.

Fully compatible with Microsoft Excel file formats (.xlsx, .csv).Supports all standard text functions including IF, SEARCH, and ISNUMBER for data extraction.Free, lightweight, and features an intuitive interface familiar to Excel users.Includes built-in advanced data tools for quick text splitting and data cleaning.

Frequently Asked Questions

Can I remove MAC addresses using the Find and Replace feature?

Find and Replace is generally ineffective for MAC addresses because each address consists of unique characters. However, if they all share an exact format, you might be able to use wildcards (like ??:??:??:??:??:??) in Word or specialized regex tools, though Excel's native Find and Replace does not fully support regex for this specific pattern.

How can I extract IP addresses if they are buried within long text sentences?

If IP addresses are mixed randomly within sentences, the simplest formulaic approach won't work. You can use Excel's Flash Fill feature by manually typing the first few IP addresses in the adjacent column and pressing Ctrl+E to train Excel to extract the rest, or use Regular Expressions (Regex) via a VBA macro.

What if my list contains IPv6 addresses instead of IPv4?

IPv6 addresses contain colons (:) instead of periods (.), meaning they share the same delimiter as standard MAC addresses. To differentiate them, you will need to filter based on string length (IPv6 addresses are typically much longer than the standard 17-character MAC address) or use more complex pattern matching via Power Query.

Excel & Data Problems