Remove special characters and numbers from an alphanumeric string

{ 4 Comments }

By Ashish Mathur · · · Leave a comment

Hi,

Assume the following alphanumeric strings in range A3:A6

A-2222 Mohan 12-4 #$% Singh$%&***
**** Sanchit #$% Pal$%&***
$^%$^$ Sudhir #$% Si$$$ngh$%&***
#@234 Krishan #$% Oman$%&***

In range B3:B6, one may want to extract only the text values i.e. drop special characters and numbers from the values in range A3:A6.  In range B3:B6, the result should be:

A Mohan Singh
Sanchit Pal
Sudhir Singh
Krishan Oman

For solving this problem, you will have to download and install the morefunc addin from here.  For instructions of installing the addin for Excel 2010, please refer point 4 of the following post.

You may refer to my solution in this workbook.

To remove only special characters appearing at the beginning and end, you may refer to my solution at the following link.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Hi All,

    could anybody suggest if we can remove only all other character from a specific cell except numeric and alhpabetical values.
    for example AB123/23-P34.
    than I need the answer as AB12323P34.

    IF it possible ?

    Thanks in advance.

    Regards,
    Rajender

    Reply

      • Hi Ashish,

        They are limited, but in each cell , number of unique values are different, and Till now I have seen seen total text length in a cell is maximum 32.
        common special characters are used which may be repeated (“/”,”_”,”(“,”)”,”.”,”-“)

        eg : ASN_10_B1/42MB_U_14M(ETH)

        Regards,
        Rajender

        Reply

        • Hi,

          If the number of special characters in a range are quite many and you wish to remove all of them, try this approach

          1. Download and install the morefunc addin from here. For instructions of installing the addin for Excel 2010, please refer point 4 of the following post.
          2. Assuming the entry is in cell B2, enter this formula in cell C2

          =TRIM(SUBSTITUTE(SUBSTITUTE(REGEX.SUBSTITUTE(SUBSTITUTE(B2," ","space"),"\W",""),"space"," "),"_",""))

          Hope this helps. Do let me know how this works.

          Reply