CleanName not cleaning " in Excel VBA?

Question

I'm updating the question to show what I have already, before adding in lines from here...

Function CleanName(strName As String) As String
'will clean part # name so it can be made into valid folder name
'may need to add more lines to get rid of other characters

    CleanName = Replace(strName, "/", "")
    CleanName = Replace(CleanName, "*", "")
    CleanName = Replace(CleanName, ".", "")
    CleanName = Replace(strName, "\", "")

End Function

Hi Matt. I'ved edited my answer based on your updated post. – Scott Holtzman Sep 24 '12 at 18:50 — Scott Holtzman, Sep 24 '12 at 18:50

score 7 · Accepted Answer · answered Sep 25 '12 at 00:25

7

Rather than multiple string replacements you could use a regexp

Function KillChars(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Global = True
.Pattern = "[\/\*\.\\""""]+"
KillChars = .Replace(strIn, vbNullString)
End With
End Function

answered Sep 25 '12 at 00:25

brettdj

54,857
16
114
177

1

+ 1 Supah! When I saw ooo solution, I thought of giving a Regexp example but then decided against it as the question was already solved :D However this is the best answer till now – Siddharth Rout Sep 25 '12 at 06:51
Here is a complete list which can be added to the above `.Pattern` < (less than) > (greater than) : (colon) " (double quote) / (forward slash) \ (backslash) | (vertical bar or pipe) ? (question mark) * (asterisk) – Siddharth Rout Sep 25 '12 at 06:57
+1 Much quicker but only if you don't `Set objRegex = `every time the function is called :) – Sep 25 '12 at 12:49
1

+1 Cleaner, quicker. Love it. I need to use this more :) Thanks brettdj – Scott Holtzman Sep 25 '12 at 13:12
@oo thanks :). I wrote this assuming it was a UDF. If the core code was processing many strings from an array, range etc, then yes it would be cleaner to embed the function within the main sub. Although for a UDF I don't think it will make a huge difference to the time overhead. – brettdj Sep 25 '12 at 23:28

score 4 · Answer 2 · edited May 23 '17 at 12:29

4

Update

Matt, to your updated post, change the code to this:

Function CleanName(strName As String) As String
'will clean part # name so it can be made into valid folder name
'may need to add more lines to get rid of other characters

    CleanName = Replace(strName, "/", "") '-> only use strName the first time, since you are passing that string to the Function
    CleanName = Replace(CleanName, "*", "")
    CleanName = Replace(CleanName, ".", "")
    CleanName = Replace(CleanName, "\", "") '-> if you use strName here, you lose your first 3 replacments
    CleanName = Replace(CleanName, """", "") '-> this is the correct syntax to remove the "
    '-> to Sid's point, this should work too
    'CleanName = Replace(CleanName, Chr(34), "")

End Function

Since others are answering, I'll change my comment to an answer to join the party!

Try

CleanName = Replace(CleanName, """", "")

You need to surround the quote in double quotes to tell VBA you want to look for the actual actual quote and not the special character it automatically recognizes. (Daniel Cook's comment below touches on it as well.)

For the benefit of others, CleanName is a custom function that cleans strings of unwanted characters. See this link for more information: CleanName

edited May 23 '17 at 12:29

Community

1
1

answered Sep 24 '12 at 18:23

Scott Holtzman

27,099
5
37
72

2

+ 1 I saw your comment after I posted my answer and was wondering as to why you posted it as a comment as it is a valid answer :) – Siddharth Rout Sep 24 '12 at 18:25
@SiddharthRout -> I guess I was thinking it was a such a simple thing, a comment was easier, but I see how that can kind of take away from the effectiveness of searches on SO for others to see. – Scott Holtzman Sep 24 '12 at 18:28
Nitpicking... If you were to "surround the quote in double quotes" then you would need to provide 5 quotation marks to indicate 1 quotation mark as a string (which as you probably know, would be auto-corrected by the IDE to be 6 quotation marks). The four quotes are as ordered. #1: Opening quotation mark indicating the start of a string. #2 & #3: Double quotation mark noting that you want a quotation mark in the string. #4: Closing quotation mark indicating the end of the string. – Daniel Sep 24 '12 at 18:42
@Daniel ... Thanks. I forgot to update my wording to be more accurate. Doing now. – Scott Holtzman Sep 24 '12 at 18:46
except if I do """"", I get an Expected: list separator or ) error message. – Matt Ridge Sep 24 '12 at 18:46
@MattRidge - I fixed that a while back. Timing might have gotten crossed. Anyway, I updated my answer based on your updated post. Check out my notes. They will help you for sure :) – Scott Holtzman Sep 24 '12 at 18:51
@MattRidge Yeah... It auto corrects if listed as in an assignment like: `string = """""` as a single line. A rule of thumb is that you need an even number of quotation marks in VBA. – Daniel Sep 24 '12 at 18:52

Siddharth Rout · Answer 3 · 2012-09-24T19:02:46.433

3

Paste this in a module

Public Function CleanName(rng As Range) As Variant
    CleanName = Replace(rng.Value, Chr(34), "")
End Function

FOLLOWUP

Option Explicit

Public Function CleanName(rng As Range) As Variant
    On Error GoTo Whoa

    Dim vVal As Variant

    vVal = rng.Value
    vVal = Replace(vVal, Chr(34), "") ' "
    vVal = Replace(vVal, Chr(42), "") ' *
    vVal = Replace(vVal, Chr(46), "") ' .
    vVal = Replace(vVal, Chr(47), "") ' /
    vVal = Replace(vVal, Chr(92), "") ' \

    CleanName = vVal
Whoa:
End Function

edited Sep 24 '12 at 19:02

answered Sep 24 '12 at 18:18

Siddharth Rout

147,039
17
206
250

You are telling me the best way to get this to work is to write the Character value? Thanks again. Sometimes I wonder how people write in this code without going crazy... – Matt Ridge Sep 24 '12 at 18:22
You can either use Chr(34) or like @nutsch suggested `""""` – Siddharth Rout Sep 24 '12 at 18:24
@MattRidge. is it the best way, I don't know. But it is one way. My answer below and nutsch's answer will also work. See my explanation for how to work with the quotes. – Scott Holtzman Sep 24 '12 at 18:25
Updated my question, to show what is being used. I'd like to use your Sid, but I don't think it will work. – Matt Ridge Sep 24 '12 at 18:44
@MattRidge: Whoa! where did the rest come from :D. I can update my answer to accommodate the rest if you want. – Siddharth Rout Sep 24 '12 at 18:47
@SiddharthRout yes I'm using your code you originally gave me a long time ago, but now I'm running into another issue I can't seem to get rid of on my own. – Matt Ridge Sep 24 '12 at 18:50
@SiddharthRout his answer is still wrong. I am getting errors... as posted above in a sec. – Matt Ridge Sep 24 '12 at 18:54
1

@MattRidge -> Please tell me where my code craps out? It works like a charm for me. If it's not working for you, I'd love to know where. – Scott Holtzman Sep 24 '12 at 19:08
I got it working, the issue was that the code below took out a \ when it shouldn't of, and joined the folder names together. I got it working, thanks :) – Matt Ridge Sep 24 '12 at 19:10

score 3 · Answer 4 · answered Sep 24 '12 at 19:21

Here's an alternative :)

Option Explicit

    Function CleanName(ByRef str As String) As String
    Dim removeChars As String
    Dim i As Long
        removeChars = "/*."""

        For i = 1 To Len(removeChars)
            str = Replace(str, Mid(removeChars, i, 1), vbNullString)
        Next i
        CleanName = str

    End Function

and to test

Sub Test()
Dim messyString As String

    messyString = "/*It Works!""."
    Debug.Print CleanName(messyString)

End Sub

CleanName not cleaning " in Excel VBA?

4 Answers4