CODEWITHSUNDEEP
excelparsingvba
DBWeinstein
DBWeinstein

Reputation: 9489

how to remove the first comma in a string in excel vba

If I have a string: "foo, bar" baz, test, blah, how do I remove a specific comma, i.e. not all of them, but just one of my choosing?

with Replace and INSTR it looks like I have not know where the comma is. The problem is, I'll only want to remove the comma if it appears between quotation marks.

So, I may want to remove the first comma and I may not.

Put more clearly, if there is a comma between a set of quotation marks, I need to remove it. if not, then there's nothing to do. But, I can't just remove all the commas, as I need the others in the string.

Upvotes: 0

Views: 1903

Answers (3)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

With some error-checking for odd number of double quotes:

Function myremove(mystr As String) As String
    Dim sep As String
    sep = """"
    Dim strspl() As String
    strspl = Split(mystr, sep, -1, vbBinaryCompare)
    Dim imin As Integer, imax As Integer, nstr As Integer, istr As Integer
    imin = LBound(strspl)
    imax = UBound(strspl)
    nstr = imax - imin
    If ((nstr Mod 2) <> 0) Then
      myremove = "Odd number of double quotes"
      Exit Function
    End If
    For istr = imin + 1 To imax Step 2
      strspl(istr) = Replace(strspl(istr), ",", "")
    Next istr
    myremove = Join(strspl(), """")
End Function

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56735

Ugh. Here's another way

Public Function foobar(yourStr As String) As String
    Dim parts() As String
    parts = Split(yourStr, Chr(34))
    parts(1) = Replace(parts(1), ",", "")
    foobar = Join(parts, Chr(34))
End Function

Upvotes: 4

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Try with Regexp in this way:

Sub foo()
    Dim TXT As String
    TXT = """foo, bar"" baz, test, blah"

    Debug.Print TXT

    Dim objRegExp As Object
    Set objRegExp = CreateObject("vbscript.regexp")

    With objRegExp
        .Global = True  '
        .Pattern = "(""\w+)(,)(\s)(\w+"")"

    Debug.Print .Replace(TXT, "$1$3$4")

    End With


End Sub

It works as expected for the sample value you have provided but could require additional adjustments by changing .Pattern for more complicated text.

EDIT If you want to use this solution as an Excel function than use this code:

Function RemoveCommaInQuotation(TXT As String)

    Dim objRegExp As Object
    Set objRegExp = CreateObject("vbscript.regexp")

    With objRegExp
        .Global = True 
        .Pattern = "(""\w+)(,)(\s)(\w+"")"

    RemoveCommaInQuotation = .Replace(TXT, "$1$3$4")

    End With


End Function

Upvotes: 5

Related Questions