Formula to eliminate all but alpha characters

excel excel-formula substitution excel-udf vba
dwwilson66 picture dwwilson66 · Mar 19, 2015 · Viewed 21.2k times · Source

I need to scrub a column of names in Excel to eliminate all non-Alpha characters including periods, commas, spaces, hyphens and apostrophes.

EXAMPLE: Change O'Malley-Smith, Tom, Jr. to OMALLEYSMITHTOMJR

The client requires this to be an Excel function, otherwise I'd make it easy with a quick Java program similar to replaceAll("[^a-zA-Z]", "").toUpperCase(). I cannot seem to find anything that looks like an off-the-shelf function to do this outside of a whole mess of SUBSTITUTE functions - which only seem to be available one-per-cell.

I'm not terribly fluent with developing custom macros if that's what I need.

Answer

pnuts picture pnuts · Mar 19, 2015

Indeed a mess of SUBSTITUTEs but within a single cell is possible, eg: 

=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),"'",""),".",""),"-",""))

Of course may need to be 'extended' to cover other non-alpha characters.

Related questions

How can I combine multiple nested Substitute functions in Excel?

I am trying to set up a function to reformat a string that will later be concatenated. An example string would look like this: Standard_H2_W1_Launch_123x456_S_40K_AB Though sometimes the "S" doesn't exist, and sometimes …

Read More
Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More