How to Delete Punctuation All Caps in Excel

1. Open the Excel 2010 spreadsheet where you want to remove punctuation and use a different case. Click the 'Developer' tab at the top of the screen. Click the 'Record Macro' button, located on the left end of the Ribbon.
2. Type 'RemovePunctuationCaps' in to the 'Macro name' field. Enter 'q' into the 'Shortcut Key' field. Click 'OK' to close the window. Click the 'Stop' button on the ribbon to stop the macro from recording.
3. Click the 'Visual Basic' button on the ribbon to launch the VBA editor. Double-click 'Module 1' on the left side of the editor. If you have already created a module in this workbook, you will need to click on the last listed module, instead of 'Module 1.' You will see 'Sub RemovePunctuationCaps()' on the right side of the window.
4. Click on the right side of the window. Drag your mouse over the green text between 'Sub Remove...' and 'End Sub.' Delete all this text so that the 'Sub' and 'End Sub' lines are all that exist.
5. Copy and paste the following code between the 'Sub' and 'End Sub' lines in the VBA editor:Dim rng As RangeFor Each rng In Selectionrng.Value = StrConv(rng.Text, vbProperCase)Next rngThis will establish a variable named 'rng' and provide the code needed to change your selection into proper case. If you would rather use lower case, change 'vbProperCase' to 'vbLowerCase.'
6. Enter the following piece of code between the last code and the ''End Sub' line:With CreateObject('vbscript.regexp').Pattern = '[^A-Za-z0-9\ ]'.Global = TrueFor Each rng In Selection.SpecialCells(xlCellTypeConstants)rng.Value = .Replace(rng.Value, vbNullString)Next rngEnd WithThis will remove all punctuation from the selected cells.
7. Click the 'X' in the upper-right corner of the editor to close it. Click on the cell or cells where you have the text you want to convert. Press 'Ctr-Q' and the macro will perform the task.