CODEWITHSUNDEEP
regexvb.netexcelvbaexpression
Asian Man
Asian Man

Reputation: 25

Excel Regular Expression: Add Quote (") to Values in Two Columns

So I have a CSV file with two columns that have items listed out like below:

enter image description here

The goal is to create a Excel VB code that will go through columns H and I, and add a quote (") to the beginning and end of each 6 digit group (e.g., H67100 into "H67100"). Additionally, the comma should be left alone.

I know the code is not complete as of yet, but this is what I have thus far. I think I am fine with the beginning part but after the match is found, I think my logic/syntax is incorrect. A little guidance and feedback is much appreciated:

Private Sub splitUpRegexPattern2()
Dim strPattern As String: strPattern = "(^[a-zA-Z0-9]{6}(?=[,])"  
Dim regEx As New RegExp  
Dim strInput As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("H:I")

If strPattern <> "" Then
    strInput = Myrange.Value
    strReplace = """" & strInput & """"

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = strPattern
    End With

 End Sub

UPDATED CODE:

Function splitUpRegexPattern2 (Myrange As Range) as String

    Dim regEx As New RegExp  
    Dim strPattern As String
    Dim strInput As String
    Dim Myrange As Range
    Dim strReplace As String
    Dim strOutput As String

    strPattern = "(^[a-zA-Z0-9]{6}(?=[,])"

    If strPattern <> "" Then
            strInput = Myrange.Value
            strReplace = """" & strInput & """"     

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = True
                .Pattern = strPattern
            End With

        If regEx.test(strInput) Then
        simpleCellRegex = regEx.Replace(strInput, strReplace)
    Else
        simpleCellRegex = "Not matched"
    End If
End If

End FUNCTION

Adding example CSV file. Download Sample CSV File

Upvotes: 0

Views: 263

Answers (1)

Sam Axe
Sam Axe

Reputation: 33738

This answer assumes you can get the values of each cell you are interested in.

There's no need to use RegEx in this case as your values appear to be simple comma-delimited data.

Public Const DOUBLE_QUOTE As String = Chr(34)
'''  
'''<summary>This function splits a string into an array on commas, adds quotes around each element in the array, the joins the array back into a string placing a comma between each element.</summary>
'''    
Public Function QuotedValues(ByVal input As String) As String
  Dim words As String() = input.Split(New Char() {","})
  Dim result As String = String.Empty

  words = (From w In words Select DOUBLE_QUOTE & w.Trim & DOUBLE_QUOTE).ToArray
  result = String.Join(", ", words)

  Return result
End Function

Upvotes: 1

Related Questions