I want to delete all non-alphanumeric characters in columns H & I, except for spaces. So if the cell says "%$187 Square" I want it to end up saying "187 Square" I have a macro that deletes the special characters AND the space, resulting in "187Square" but I need that space to not be deleted. Please advise Code: Sub RemoveNonANCharacters() Dim x As Long, Text As String, Cell As Range, Rng As Range Set Rng = Range("H:I") For Each Cell In Rng Text = Cell.Value For x = 1 To Len(Text) If Mid(Text, x, 1) Like "[!A-Za-z0-9]" Then Mid(Text, x) = Chr(1) Next Cell = Replace(Text, Chr(1), "") Next End Sub
@el gnomo your love of excel could come in handy here @kevC Harold plz @mrm32 oil and gas skills help
The only solution is to concatenate your V-Lookup and utilize SQL. Unfortunately, there is no other way.
Can't you just change the line with the LIKE to : If Mid(Text, x, 1) Like "[!A-Za-z0-9]" And Mid(Text, x, 1) <> " " Then Mid(Text, x) = Chr(1)
Did you put a space between the quotes? Try "[ ]" too. However, to avoid an extra Mid call, try putting the space in your original LIKE statement like this*: If Mid(Text, x, 1) Like "[!A-Za-z0-9 ]" Then Mid(Text, x) = Chr(1) *note the space after the 9. I think the VBA Like format matches that way. fwiw: that For Loop looks horribly inefficient, if you have large amounts of cells to change. Have you considered just using the Regex.Replace feature of VBA, instead? That would replace the *entire* FOR LOOP and the line after the NEXT. You might have reasons to not use that. But if so, that's going to be faster than calling Mid *TWICE* for every character of every cell. Let us know, and we can share how to do that without a For Loop.
You should use regex. Are the non alpha character only at the start or end? In that case do ^(.*)[A-Za-z0-9] or [A-Za-z0-9](.*)$ and then delete the matching string using a replace.
Yeah putting that space in there did the trick. It dawned on me after posting this morning. But, as you said, it's very inefficient. Takes way too long to run. I did consider the regex route but I have never used it before. Not sure how to get it to work. If someone could help that would be great!!
Here's your code using Regex instead. Notice no inner FOR LOOP. the RegEx.Replace call operates on all matches in the string at once....if global=true is on. Code: Sub RemoveNonANCharacters() Dim x As Long, Text As String, Cell As Range, Rng As Range Dim RegEx As Object Set RegEx = CreateObject("VBScript.RegExp") RegEx.Global = True RegEx.Pattern = "[^A-Za-z0-9 ]" Set Rng = Range("H:I") For Each Cell In Rng Cell = RegEx.Replace(Cell.Value, "") Next End Sub note: you usually have to "turn on" Regular Expression for your code. See this for how to enable the RegExp Object. https://stackoverflow.com/questions...ct-regular-expression-using-vba-macro-in-word note note: the pattern will use a carat ^ rather than ! for "not" such and such char. see my code.
Odd. Works when I try it on your example string in my Excel. Make sure there's a space in the quotes. Oh well, I agree with the others, though -- on a large spreadsheet, that thing's going to take forever to churn. If you're just doing a few dozen rows, it's not a huge deal, but if you're doing hundreds or thousands of rows, it is. I accidentally ran it on an entire spreadsheet last night and, dang, that was a mistake.
I'm still waiting for What to come in and say how the best way is for everyone to stop using Excel, install Linux with dual boot, then use Gnumeric..."It's much easier."
Substitute all spaces with a keyword not found in the list, for example "clutchfans" first. Run you macro. Then replace all "clutchfans" with a " "
