none
How to extract strings in between quotes in VBA RRS feed

  • Question

  • COLUMN A DLBL GPFGBSM,'PHGP.GPFGBSM.GBS.KSDS',,VSAM,CAT=PCAT2 DLBL GPFGGIM,'PHGP.GPFGGIM.GGI.KSDS',,VSAM,CAT=PCAT2 36 DLBL GPFEXT1,'PHGP.GPFPWO4.PWO.ESDS',,VSAM,CAT=PCAT3 DLBL FILE01,'PHGP.GPFPWO4.PWO.ESDS',,VSAM,CAT=PCAT3 DLBL FILE1,'PHGP.GPFPWO4.PWO.ESDS',,VSAM,DISP=(NEW,DELETE)

    I have a spreadsheet which contains many file names inside each single cell under column A

    (as shown here,there are other file names as well).Now I must write only the part between ' '.

    for example : I want to replace each cells with only the file name i.e. PHGP.GPFGBSM.GBS.KSDS.

    Is that possible?

    So far I have tried this:

    Sub Search3()
    Dim MatchString As String
    Dim matchstrin2 As String
    Dim counter As Variant
    Dim Name As String
    Dim Datain As String
    MatchString = "DLBL"
    MatchString2 = "DLBL  "
    For counter = 1 To Range("A:A").Count
    Datain = Range("A" & counter).Value
    If (InStr(1, Datain, MatchString) > 0 Or InStr(1, Datain, MatchString2) > 0) Then
    Data = Split(Datain, vbLf)
    cnt = UBound(Data)
        For I = 0 To cnt
            If Data(I) Like "*DLBL*" Then
            openPos = InStr(Data, "")
            closePos = InStr(Data, "'")
             midbit = Mid(Data, openPos + 1, closePos - openPos - 1)
            Range("B" & counter).Value = midbit
            'Next
            End If
            
            Next
         midbit = ""
    End If
    Next counter
    End Sub

    Its giving me type mismatch error.Please help


    • Edited by Subhra90 Thursday, May 22, 2014 8:42 PM
    Thursday, May 22, 2014 8:39 PM

All replies

  • I don't think I would have done it that way, but if you get the syntax right and eliminate the typos it should work

    Sub Search3()
    Dim MatchString As String
    Dim MatchString2 As String
    Dim counter As Long, i As Long
    Dim openPos As Long
    Dim closePos As Long
    Dim midbit As String
    Dim Name As String
    Dim DataIn As String
    Dim vData As Variant
        MatchString = "DLBL"
        MatchString2 = "DLBL  "
        For counter = 1 To Range("A" & Rows.Count).End(-4162).Row
            DataIn = Range("A" & counter).Value
            If InStr(1, DataIn, MatchString) > 0 Or InStr(1, DataIn, MatchString2) > 0 Then
                vData = Split(DataIn, vbLf)
                For i = 0 To UBound(vData)
                    If vData(i) Like "*DLBL*" Then
                        openPos = InStr(1, vData(i), "'")
                        closePos = InStr(openPos + 1, vData(i), "'")
                        midbit = Mid(vData(i), openPos + 1, closePos - openPos - 1)
                        Range("B" & counter).Value = midbit
                    End If
                Next
                midbit = ""
            End If
        Next counter
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, May 23, 2014 6:07 AM