If you’re looking for a formula to replace non-alphanumeric characters there are several combinations of functions that you can use to strip unwanted characters from entire columns. Right off the bat, I’m going to steer you in the direction of using a macro to do the job. It will be much faster, especially if you’re planning on stripping unwanted characters from thousands of cells. If you’ve never used an Excel macro before it is very simple, and you can in fact create your own Excel formula with a VB macro that you can use like any other, like =sum(A2:A5) or =count(A3:A9).
If you haven’t done so already, enable the Developer toolbar in Excel and then click the Macro button to create a new macro. The macro below can be run in the Visual Basic Editor to quickly strip cells of non-alphanumeric characters, or incorporated into another function.
Sub CleanAll() Dim rng As Range Application.ScreenUpdating = False ' Turns off screen updating On Error Resume Next ' Macro will continue if it encounters #N/A 'Adjust "Sheet1" and Range in Line 6 For Each rng In Sheets("Sheet1").Range("A1:A10").Cells rng.Value = AlphaNumericOnly(rng.Value) ' Function call Next Application.ScreenUpdating = True End Sub Function AlphaNumericOnly(strSource As String) As String Dim i As Integer Dim strResult As String On Error Resume Next For i = 1 To Len(strSource) Select Case Asc(Mid(strSource, i, 1)) 'The numbers below match Char Codes to keep Case 32, 35 To 47, 48 To 57, 63, 65 To 90, 92, 96, 97 To 122: strResult = strResult & Mid(strSource, i, 1) End Select Next AlphaNumericOnly = strResult End Function
Line 19 is the most important. It’s in line 19 that you tell Excel which characters you want to keep, not the ones you want to remove. It’s easier this way since the number of alphanumeric characters is well-defined, but the number of characters you want removed is not. Excel has numbers to represent each character, so each number or range of numbers in line 19 represents a character or characters, shown below. There is a complete list of character codes over at Microsoft Office’s site. Note that you also need to update the Sheet name and range of cells that you want to run the macro on in line 6.
Once you’re done creating your macro, put your cursor into the Sub CleanAll() section and click “Play” in the VB Editor to run your macro.
|Char Code||Character||Char Code||Character|