Hi
i want to remove all special characters except # - & , and .
Thanks.
Hi
i want to remove all special characters except # - & , and .
Thanks.
This is the VBA method I use for this. First we add a new function to your workbook.
How to install the User Defined Function:![]()
Please Login or Register to view this content.
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#-.
Last edited by JBeaucaire; 01-13-2014 at 05:55 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thank you for ur help..i need commas too
An alternative approach:
![]()
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
how to run it after installing the above program
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.
but it removes spaces![]()
hi
it shows error as compile error and variable not defined
wherever the special characters are removed i want to fill that with single space
I've amended my function, try this:
![]()
Please Login or Register to view this content.
Thank you..it works![]()
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks