Extracting values in between quotes

non-programmatic solution here (although I like slycoder's functions).

 

In both cases, original strings are filled in one column and substring functions inserted to the left.  The substring label is inserted in row 1 on top and the formulas are almost identical except for the label reference (which must be exactly as found in the original string.

startDate (cell A2)
=MID(D2,FIND(A$1,D2)+(LEN(A$1)+2),FIND(CHAR(34),MID(D2,FIND(A$1,D2)+(LEN(A$1)+3),LEN(D2))))

metric (cell B2)
=MID(D2,FIND(B$1,D2)+(LEN(B$1)+2),FIND(CHAR(34),MID(D2,FIND(B$1,D2)+(LEN(B$1)+3),LEN(D2))))

endDate (cell C2)
=MID(D2,FIND(C$1,D2)+(LEN(C$1)+2),FIND(CHAR(34),MID(D2,FIND(C$1,D2)+(LEN(C$1)+3),LEN(D2))))

These formulas can be copied down to produce more results.

-Glenn

 EE-substrings.xlsx

An example is in A1
=MID(A1,FIND("startDate=",A1,1)+11,10)
=MID(A1,FIND("metric=",A1,1)+8,LEN(A1)-FIND("metric=",A1,1)-8-FIND(" endDate",A1,1)-16)
=MID(A1,FIND("endDate=",A1,1)+9,10)
20111206-EE-extractBetweenQuotes.xlsx

Hi all,

thanks for your quick replies. It is much appreciated.
I have tested your solutions.

Here are my takes:

1) Slycoder: using the function as intended work perfectly... but is limited when dealing with spreadsheets with long columns of yearly data formatted in the same manner.

Situation: When the =GetValue("value","a1") formula is copied and pasted into a long column, the "a1" part doesn't update automatically to match the subsequent "a4", "a5", "a6", etc. I have to manually change the cell number within the quotes.

May I suggest the following improvement:? Make the "a1" in the formula as a standard excel cell reference without quotes such as =GetValue("value", a1)? Yes/No?

Question: can you consider upgrading the function? Yes/No?

I am strongly considering your solution if the suggested mod can be implemented to your current function.

 2) GlennLRay: your formula works flawlessly. Also, I like the column headers as the basis for the formula, it is the exactly how the data is going to end up.  

I will provide you some points but will wait until I hear from Slycoder.

3)TigerMan: thanks for your efforts. However, I will stick with Slycoder' and GlennlRay' solutions as they were the first to answer back.

Very practical  function for capturing data in-between quotes.

Only one limitation makes this function less than great:
In =GetValue("value","cell"),  the string that calls the "cell" to look for
is itself in-between quotes therefore is not dynamic.

It must be manually changed to point to each cell as it doesn't update itself upon being copied down a column or through a row.  

Otherwise, as previously said, very practical function to capture data in-between quotes.

Sorry for the delay - life got in the way -


Question: can you consider upgrading the function?  - Yes  Working on a solution now.

Here is the fix - just changed 2 lines:

' Usage: =GetValue("String", a1)

Option Explicit

Public Function GetValue(myVar As String, myRange As Range) As String
    Dim myCell As String
    Dim myPos As Long
    ' Get value from value pair
   
    ' Find item before the equals sign
    If Len(myRange) > 0 And Len(myVar) > 0 Then
       
        ' Get range value and position
        myCell = myRange.Value
        myPos = InStr(1, UCase(myCell), UCase(myVar))
       
       
        If myPos > 1 Then
            ' Get Item From Quotes
            GetValue = GetItemFromQuotes(myCell, myPos)
           
        End If
    End If
End Function

Public Function GetItemFromQuotes(myCell, myPos)
    Dim myStart As Long
    Dim myEnd As Long
   
    myStart = InStr(myPos, myCell, Chr$(34))
    myEnd = InStr(myStart + 1, myCell, Chr$(34))
   
    GetItemFromQuotes = Mid(myCell, myStart + 1, myEnd - myStart - 1)
   
End Function

Updated spreadsheet attached - for future reference.
20111213-EE-extractBetweenQuotes.xlsm

Hey Slycoder,

Great to hear from you. Awesome!
This makes your function very useful and intuitive to use.

Unfortunately, I can't update the comment left earlier upon accepting your initial solution in order to reflect your latest update. Nevertheless, I am very satisfied with the current solution.  
 
Your time is much appreciated.
Thx