how remove all special characters except # , & - .

This is the VBA method I use for this. First we add a new function to your workbook.

Please Login or Register  to view this content.

How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use.


To use it in a cell like any other formula like so:

=CLEANSPECIAL(A1)

The results will be only A-Za-Z0-9#-.

An alternative approach:

Please Login or Register  to view this content.

Originally Posted by JBeaucaire

How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use.


To use it in a cell like any other formula like so:

=CLEANSPECIAL(A1)

The results will be only A-Za-Z0-9#-.

As explained...

I named my function NotSpecial so in my case you would use:

Formula:

Please Login or Register  to view this content.

Take a look at the code. You'll see the line of code where we listed all the characters to include, highlighted in red in post #2. Add a comma in there.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I've amended my function, try this:

Please Login or Register  to view this content.