Excel Formula to Remove Non-Alphanumeric Characters

Cleaning messy, non-alphanumeric characters from Excel datasets is a tedious struggle that frequently derails database management. While standard IT budgets typically fund expensive external cleansing tools to resolve this, leveraging internal Excel capabilities offers a more efficient path. Utilizing native formulas grants users immediate, automated data-cleaning power without additional software overhead.

Under the stipulation that your organization uses Excel 365 or 2021, dynamic arrays provide robust solutions. For instance, combining nested functions like REDUCE and LAMBDA serves to systematically strip unwanted symbols. Below, we present the exact, step-by-step formula to effortlessly purify your text fields.

When working with imported databases, legacy system reports, or user-submitted forms in Microsoft Excel, you will often find text strings cluttered with unwanted characters. Extra punctuation, spaces, symbols, and special characters (like #, @, -, or _) can easily break matching functions like VLOOKUP, XLOOKUP, or MATCH. To ensure your data is clean and uniform, you need a way to strip away everything except alphanumeric characters (letters and numbers).

Unlike database engines or standard programming languages, legacy versions of Excel do not feature a simple, built-in function to remove non-alphanumeric characters. However, depending on your version of Excel, there are several highly effective ways to solve this problem. This guide will walk you through the five best methods, ranging from the brand-new 365 formulas to robust VBA solutions and powerful Power Query techniques.

Method 1: The Modern Excel 365 Way (Using REGEXREPLACE)

If you are using the latest version of Microsoft 365 (Insider or Current Channel updates), Microsoft has finally introduced native Regular Expression functions. This completely revolutionizes text cleaning in Excel.

The REGEXREPLACE function allows you to search for a specific pattern of characters in a text string and replace them with something else-in this case, an empty string (nothing).

The Formula

=REGEXREPLACE(A2, "[^A-Za-z0-9]", "")

How It Works

• A2: The cell containing the messy target text.
• "[^A-Za-z0-9]": This is the regular expression pattern.
  • The square brackets [] define a character set.
  • The caret symbol ^ at the start of the bracket means "NOT".
  • A-Z matches any uppercase letter, a-z matches any lowercase letter, and 0-9 matches any digit.
  • Combined, [^A-Za-z0-9] means "any character that is NOT a letter or a number".
• "": This is the replacement text. Since it is empty, all matched non-alphanumeric characters are deleted.

Tip: If you want to keep spaces while removing all other symbols, simply add a space inside the bracket: "[^A-Za-z0-9 ]".

Method 2: The Native Array Formula (Excel 365 & Excel 2021)

If you have Microsoft 365 or Excel 2021 but do not yet have access to the newly released REGEXREPLACE function, you can build a dynamic array formula using CONCAT, MID, SEQUENCE, and LEN.

The Formula

=CONCAT(IF(ISNUMBER(SEARCH(MID(A2,SEQUENCE(LEN(A2)),1),"abcdefghijklmnopqrstuvwxyz0123456789")),MID(A2,SEQUENCE(LEN(A2)),1),""))

How It Works

This formula deconstructs the text string into individual characters, checks each one, and glues the allowed characters back together:

1. SEQUENCE(LEN(A2)): Generates an array of sequential numbers from 1 to the total length of the string in A2. For example, if A2 contains "A-1", it generates {1, 2, 3}.
2. MID(A2, SEQUENCE(...), 1): Extracts each character one-by-one, converting "A-1" into the array {"A", "-", "1"}.
3. SEARCH(..., "abc...9"): Checks where each character falls within our allowed alphanumeric list. If the character is found, it returns its position (a number); if not, it returns a #VALUE! error.
4. ISNUMBER(...): Converts numbers to TRUE and errors to FALSE. For "A-1", this results in {TRUE, FALSE, TRUE}.
5. IF(..., MID(...), ""): If the evaluation is TRUE, it keeps the character. If FALSE, it replaces it with an empty string "". This produces {"A", "", "1"}.
6. CONCAT(...): Concatenates the array back into a single string: "A1".

Method 3: VBA User-Defined Function (All Excel Versions)

For users on older versions of Excel (such as Excel 2019, 2016, or 2013), array formulas can be slow, and native RegEx functions do not exist. In this scenario, writing a simple User-Defined Function (UDF) in VBA is the most robust and backward-compatible solution.

The VBA Code

To implement this, press Alt + F11 to open the VBA Editor, click Insert > Module, and paste the following code:

Function CleanText(Txt As String) As String
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Global = True
        .IgnoreCase = True
        .Pattern = "[^A-Za-z0-9]"
    End With
    CleanText = RegEx.Replace(Txt, "")
End Function

How to Use It

Once you close the VBA window and return to your Excel sheet, you can use this macro just like a regular formula:

=CleanText(A2)

This method runs incredibly fast and keeps your worksheet formulas clean and easy to read. Remember to save your workbook as an Excel Macro-Enabled Workbook (.xlsm).

Method 4: Cleaning Data with Power Query (No Formulas Required)

If you are processing large tables of data with thousands of rows, formulas can bog down your workbook's performance. Power Query is Excel's built-in data preparation engine, and it is highly optimized for cleaning text without complex formulas.

Step-by-Step Guide:

1. Select any cell within your data table.
2. Go to the Data tab on the Ribbon and click From Table/Range. This opens the Power Query Editor.
3. Go to the Add Column tab and click Custom Column.
4. Name your new column (e.g., "Cleaned Data").
5. In the formula box, enter the following Power Query formula:

   Text.Select([YourColumnName], {"a".."z", "A".."Z", "0".."9"})

   Note: Replace [YourColumnName] with the actual header of the column you want to clean. Power Query is case-sensitive, so write the formula exactly as shown.
6. Click OK.
7. Go to the Home tab, click Close & Load, and your clean data will load into a fresh Excel sheet.

Method 5: Traditional Nested SUBSTITUTE (For Specific Characters)

If you are stuck on an older version of Excel, cannot use macros, and only have a few known non-alphanumeric characters to remove (such as spaces, dashes, and periods), you can nest multiple SUBSTITUTE functions inside one another.

The Formula

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", ""), ".", "")

When to Use This

While this is not a true "catch-all" solution for every non-alphanumeric character, it is perfect for basic tasks like cleaning phone numbers or standard product SKUs where you only expect to see dashes or spaces.

Choosing the Best Method for Your Needs

To help you decide which approach to use, refer to this quick comparison table:

Summary

Cleaning up non-alphanumeric characters doesn't have to be a manual process. If you are on the cutting edge of Excel, leverage the power of REGEXREPLACE. If you are preparing automated monthly reporting pipelines, look no further than Power Query. For legacy spreadsheets that need to work across different offices, VBA is your most reliable ally. Implement the option that matches your setup, and keep your data clean and uniform!