Using VBA to Identify and remove " (quotation marks) from start and end of a string

[CarolynS]

Hi

I'm fairly new to VBA so please bear with me

I have a data set that contains a list of course names in one of the columns. For some unknown reason, the system that produces the data likes to put quotation marks around some (but not all) of the course names. I need to remove the quotation marks at the start and end of the string but not all quotation marks as there may be some legitimately contained within the course name.

I was originally going to use the following code to filter on the relvant column (ColRef) for records beginning with ".

'With Worksheets("Utilisation Regs Raw") '.AutoFilterMode = False
'.Range("A1").AutoFilter
'.Range("A1").AutoFilter Field:=Colref, Criteria1:="*"""

'End With

(sorry I don't know how to get my code in to those blue boxes - this is my 1st ever post!)

However, I then realised I didn;t know how to loop through just the filtered rows to remove the 1st and last characters from each string. I therefore decided to try using an if statement and for next loop:


For p = 2 To FinalRow
If Cells(p, Colref).Value = "*Chr(34)" Then
mystring = Cells(p, Colref).Value
Cells(p, Colref).Value = Mid(mystring, 2)

End If

Next p

However, I can't work out how to say begins with " . I tried using ="*""" but that didn't work so tried to use Chr(34) (as above). I'm hoping it's something really simple but I just don't know what the correct expression is.

Thanks in advance

 

[MickG]

Try this for Column "A" data.

[COLOR="Navy"]Sub[/COLOR] MG28Oct36
im Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dn.Value = Left(Mid(Dn.Value, 2), Len(Mid(Dn.Value, 2)) - 1)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[CarolynS]

Try this for Column "A" data.

[COLOR=navy]Sub[/COLOR] MG28Oct36
im Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Dn.Value = Left(Mid(Dn.Value, 2), Len(Mid(Dn.Value, 2)) - 1)
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thanks Mick. Does this remove the 1st and last character from every cell or just those that start with "? There will be a mix of strings beginning with " and strings beginning with a letter. I only want to remove the 1st and last characters if they are ", all other cells should be untouched. In my data, if the string begins with " it will always end with ". The data i'm lookig at is currently in column 16 but the system i export the data from likes to randonly mix up the column order from time to time so I've captured the column number in a variable earlier on my code (not shown above - variable is ColRef). In your code above can i swtich the "A" for ColRef?
Thanks

 

[CarolynS]

P.s. how do you get your code in to that box when you post?

 

[MickG]

Try this:-
NB:- See "ColRef" in Code;-

[COLOR="Navy"]Sub[/COLOR] MG28Oct47
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] ColRef [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
ColRef = 6 '[COLOR="Green"][B] Just an Example,Change to suit[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(1, ColRef), Cells(Rows.Count, ColRef).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Left(Dn.Value, 1) = Chr(34) And Right(Dn.Value, 1) = Chr(34) [COLOR="Navy"]Then[/COLOR]
    Dn.Value = Left(Mid(Dn.Value, 2), Len(Mid(Dn.Value, 2)) - 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

NB:- Use "Hash" Sign for code Tags :- Next to "Quote" Sign.
Regards Mick

 

[CarolynS]

Thank you Mick that has worked . I have altered my original code slightly and gone with the below to get the same result. I was getting hug up on trying to use begins with rather than Left!

    For p = 2 To FinalRow
            If Left(Cells(p, ColRef), 1) = """" Then
            mystring = Cells(p, ColRef).Value
            Cells(p, ColRef).Value = Left(Mid(mystring, 2), Len(Mid(mystring, 2)) - 1)
    
        End If
    
   Next p

Thanks for your help with this

 

[MickG]

You're welcome