Remove special characters from an Excel 365 worksheet
Removing letters and special characters from cells/columns
How to remove special character from excel column
How can I replace all special characters in a cell except for spaces and dashes in Google Sheets using regex? - Stack Overflow
You can adapt and run the following macro:
Sub RemoveChars()
Dim v
Application.ScreenUpdating = False
' Modify as needed
For Each v In Array("#", "$", "%")
Cells.Replace What:=v, Replacement:="", LookAt:=xlPart
Next v
Application.ScreenUpdating = True
End Sub
Add characters to be removed to the array.
If you store the macro in your Personal Macro Workbook PERSONAL.XLSB, you can run it whenever you need it.
See Excel Personal Macro Workbook | Save & Use Macros in All Workbooks if you need more information.
depends on the symbols.
Importing the data into PowerQuery automatically removed $ and converted % to matching percent value.
.
Then you can use Text.Remove (to delete specific char) or Text.Select function (to keep only listed characters, ie A-z and 0-9) to get rid of any other characters. Which ever function works better for you. https://www.youtube.com/watch?v=II4wvIifIQg Then the only remaining trick is to add code to handle variable number of columns. And I've seen that done with some relatively simple hand coding.
.
I promise I have been trying to figure this out for at least a week (google, AI, YouTube, etc);
Full disclosure I am pretty new to using excel in the capacity that I find myself in now.
Repeatedly I am evaluating data via csv files that contain words, letters, and numbers within a single cell. For example cell D,3 contains--Hi Reddit------>I'm Frank I am 63 and weigh 218 lbs.
What I am looking to extract/automate/macro/power query is the numbers and have the numbers separated by a word, let's just use feet.....
My desired outcome would scrub the sentence from-- Hi Reddit------>I'm Frank I am 63 and weigh 218 lbs.
to-- 63 feet 218 feet
This is a very repetitive situation I deal with daily now. Would love to have a workbook set up that I can copypasta, import whatever. But I will take whatever I can get.
Thank you in advance! I've been lurking here for a bit and I've seen the problem solving accomplished, so I'm sure there is a solution. I'm just really at my capacity right now on how to get this to work
It would be easier to set the special characters you want to remove/replace since you can control which ones are the characters to be removed.
Formula:
=REGEXREPLACE(A1, "[!@#$%^&*()]", "")
Sample:
Or set the characters only allowed. (replace all characters that are not numbers, letters, space and dash)
Formula:
=REGEXREPLACE(A1, "[^0-9a-zA-Z -]", "")
Sample:
Reference:
- For the syntax google is using, see link
Just use this: =REGEXREPLACE(A1, "[^a-zA-Z0-9\-\s]", "")
That will remove all characters that aren't a letter, a digit, a dash or a space.
Example: https://docs.google.com/spreadsheets/d/1LLL9hKdm035fikv96KjP3sAZTIcKs4GqBuxWHHsVO7g/edit?usp=sharing
