Press enter to see results or esc to cancel.

Excel: Remove Non-Alphanumeric Characters with Macro

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.

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
32 space 80 P
33 ! 81 Q
34 82 R
35 # 83 S
36 $ 84 T
37 % 85 U
38 & 86 V
39 87 w
40 ( 88 X
41 ) 89 Y
42 * 90 Z
43 + 91 [
44 , 92 \
45 93 ]
46 . 94 ^
47 / 95 _
48 0 96 `
49 1 97 a
50 2 98 b
51 3 99 c
52 4 100 d
53 5 101 e
54 6 102 f
55 7 103 g
56 8 104 h
57 9 105 i
58 : 106 j
59 ; 107 k
60 < 108 l
61 = 109 m
62 > 110 n
63 ? 111 o
64 @ 112 p
65 A 113 q
66 B 114 r
67 C 115 s
68 D 116 t
69 E 117 u
70 F 118 v
71 G 119 w
72 H 120 x
73 I 121 y
74 J 122 z
75 K 123 {
76 L 124 |
77 M 125 }
78 N 126 ~
79 O 127 DEL
  • guest

    hi ,
    not work there are error code “syntaxerror” 20. line