replace double quote with specific code

saidhruv · Feb 9, 2017

Can you help me with a formula to replace double quotes available within a string with specific code? All odd double quotes have to be marked as 'lq' and all even double quotes to be marked as 'rq'.
A logic that has to be added is, in case of odd double quotes, the right most double quote is to be kept as it is and hence not to be replaced with code. Hence, in case string has only one double quote, it is not to be replaced.

Example:
Input: NEW"YORK Output: NEW"YORK
Input: NEW"YORK" Output: NEWlqYORKrq
Input: NEW"YO"R"K Output: NEWlqYOrqR"K
Input: NE"W"YO"R"K Output: NElqWrqYOlqRrqK

Asheesh · Feb 9, 2017

To my little knowledge it is a tricky one as far as formula solution is concerned..so could you please confirm if you are ok with VBA solution..

Btw, what is the maximum number of quotes a string can have..is there any limitation.

Asheesh · Feb 10, 2017

Hope the attached helps..!!

Code:

Option Explicit
Dim MyRng As Range, Cell As Range
Dim tQuote As Integer, cQuote As Integer, n As Integer, nQuote As Integer
Dim sChr As String, cType As Boolean

Sub RemoveQuotes()

Set MyRng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

For Each Cell In MyRng
  
  tQuote = Len(Cell.Value) - Len(Application.WorksheetFunction.Substitute(Cell.Value, Chr(34), ""))
  
  If tQuote = 1 Then
  
  Cell.Offset(, 1).Value = Cell.Value
  
  Else
  
  Cell.Offset(, 1).ClearContents
  
  cType = (tQuote Mod 2 = 0)
  
  Select Case cType
  
  Case False
  
  cQuote = tQuote - 1
  
  nQuote = 0
  
  For n = 1 To Len(Cell.Value)
  
  sChr = Mid(Cell.Value, n, 1)
  
  If sChr = Chr(34) Then
  
  nQuote = nQuote + 1
  
  If nQuote <= cQuote And nQuote Mod 2 <> 0 Then
  
  Cell.Offset(, 1).Value = Cell.Offset(, 1).Value & "lq"
  
  ElseIf nQuote <= cQuote And nQuote Mod 2 = 0 Then
  
  Cell.Offset(, 1).Value = Cell.Offset(, 1).Value & "rq"
  
  ElseIf nQuote > cQuote Then
  
  Cell.Offset(, 1).Value = Cell.Offset(, 1).Value & Chr(34)
  
  End If
  Else
  
  Cell.Offset(, 1).Value = Cell.Offset(, 1).Value & sChr
  
  End If
  
  Next n
  
  Case Else
  
  nQuote = 0
  
  For n = 1 To Len(Cell.Value)
  
  sChr = Mid(Cell.Value, n, 1)
  
  If sChr = Chr(34) Then
  
  nQuote = nQuote + 1
  
  If nQuote Mod 2 <> 0 Then
  
  Cell.Offset(, 1).Value = Cell.Offset(, 1).Value & "lq"
  
  ElseIf nQuote Mod 2 = 0 Then
  
  Cell.Offset(, 1).Value = Cell.Offset(, 1).Value & "rq"
  
  End If
  Else
  
  Cell.Offset(, 1).Value = Cell.Offset(, 1).Value & sChr
  
  End If
  
  Next n
  
  End Select
  
  End If
  
Next Cell

End Sub

saidhruv · Feb 11, 2017

Asheesh said:
To my little knowledge it is a tricky one as far as formula solution is concerned..so could you please confirm if you are ok with VBA solution..

Btw, what is the maximum number of quotes a string can have..is there any limitation.

Dear Asheesh, the number of quotes can be more than 24. I guess we can keep a limit of 30. Btw, can we keep it n?

Asheesh · Feb 12, 2017

I shall try for a formula solution..And get back to you if I get to something

saidhruv · Feb 12, 2017

Asheesh said:
I shall try for a formula solution..And get back to you if I get to something

Dear Asheesh .... no no do not work on any thing else.. Your code works super perfectly and for any number of quotes within single string.. it is generic and super quick.. Thanks again.

saidhruv · Feb 13, 2017

Dear Asheesh - while working, i noticed that for few rows the code automatically removes a space just after an integer. However, it is not consistent. I couldnt debug. Can you pls check. Attaching the file here. Pls check data in A1 which is input while B1 is output. In B1 you can notice that space after 2100 is not available.

John Jairo V · Feb 13, 2017

Hi to all!

For your first question, I have anothers options.

1. An UDF approach. You can use the Function freplacequotes. This is the code:

Code:

Function freplacequotes$(s$)
    Dim q&, a$, i&
   
    With Application
        q = Len(s) - Len(.Substitute(s, Chr(34), ""))
        If q < 2 Then
            freplacequotes = s
        Else
            a = s
            For i = 1 To q \ 2
                a = .Substitute(a, Chr(34), "lq", 1)
                a = .Substitute(a, Chr(34), "rq", 1)
            Next i
            freplacequotes = a
        End If
    End With
End Function

2. A Macro approach. Please clic into the shape. This is the code:

Code:

Sub replacequotes()
    Dim q&, a$, i&
    Dim s As Range
   
    With Application
        .ScreenUpdating = False
        Range("B1", [B1048576].End(xlUp)).ClearContents
        For Each s In Range("A1", [A1048576].End(xlUp))
            q = Len(s) - Len(.Substitute(s, Chr(34), ""))
            If q < 2 Then
                s.Offset(, 1) = s
            Else
                a = s
                For i = 1 To q \ 2
                    a = .Substitute(a, Chr(34), "lq", 1)
                    a = .Substitute(a, Chr(34), "rq", 1)
                Next i
                s.Offset(, 1) = a
            End If
        Next s
        .ScreenUpdating = True
        Set s = Nothing
    End With
End Sub

Check file. Blessings!

saidhruv · Feb 14, 2017

John Jairo V said:

Hi to all!

For your first question, I have anothers options.

1. An UDF approach. You can use the Function freplacequotes. This is the code:

Code:

Function freplacequotes$(s$)
    Dim q&, a$, i&
  
    With Application
        q = Len(s) - Len(.Substitute(s, Chr(34), ""))
        If q < 2 Then
            freplacequotes = s
        Else
            a = s
            For i = 1 To q \ 2
                a = .Substitute(a, Chr(34), "lq", 1)
                a = .Substitute(a, Chr(34), "rq", 1)
            Next i
            freplacequotes = a
        End If
    End With
End Function

2. A Macro approach. Please clic into the shape. This is the code:

Code:

Sub replacequotes()
    Dim q&, a$, i&
    Dim s As Range
  
    With Application
        .ScreenUpdating = False
        Range("B1", [B1048576].End(xlUp)).ClearContents
        For Each s In Range("A1", [A1048576].End(xlUp))
            q = Len(s) - Len(.Substitute(s, Chr(34), ""))
            If q < 2 Then
                s.Offset(, 1) = s
            Else
                a = s
                For i = 1 To q \ 2
                    a = .Substitute(a, Chr(34), "lq", 1)
                    a = .Substitute(a, Chr(34), "rq", 1)
                Next i
                s.Offset(, 1) = a
            End If
        Next s
        .ScreenUpdating = True
        Set s = Nothing
    End With
End Sub

Check file. Blessings!

Dear John Jairo V - thanks a lot.. i used the Function. And it works perfectly fine.. i am sorry, but i couldnt check or use macro cuz the work is done thru function itself...

Asheesh · Feb 23, 2017

@John Jairo V

I like it..but I have a question, I have seen some people use $ and & special characters along with variables as you did above. I have been using VBA for quite sometime now, but not really sure about the significance of these special characters along with variables.

Appreciate if you could shed some light.

Marc L · Feb 23, 2017

Hi !

It's just about old BASIC compatibility to declare variables
as explained in VBA inner help of any type data like Long for example …

John Jairo V · Feb 23, 2017

Hi @Asheesh

Its just an abbreviation for declare variables:

Code:

Dim a as Integer --> Dim a%
Dim a as Long --> Dim a&
Dim a as Single --> Dim a!
Dim a as Double --> Dim a#
Dim a as String --> Dim a$
Dim a as Variant --> Dim a

Blessings!

Marc L · Feb 24, 2017

Or to declare on the run the first time variable is used in code

like for example in VBA R& = 1 equals in VB Dim R as Long = 1 …

And Dim C@ is like Dim C as Currency …

replace double quote with specific code

saidhruv

Member

Asheesh

Excel Ninja

Asheesh

Excel Ninja

Attachments

saidhruv

Member

Asheesh

Excel Ninja

saidhruv

Member

saidhruv

Member

Attachments

John Jairo V

Well-Known Member

Attachments

saidhruv

Member

Asheesh

Excel Ninja

Marc L

Excel Ninja

John Jairo V

Well-Known Member

Marc L

Excel Ninja