Extracting values in between quotes

Hi,

Objective: extract data stored in between quotes in the following strings.

a) string #1:
<appMetrics startDate="2011-11-01" metric="Sessions" endDate="2011-11-30" version="1.0" generatedDate="12/6/11 11:31 AM">

Data to extract from:
- startDate
- metric
- endDate

Values to return:
- 2011-11-01
- Sessions
- 2011-11-30

b) The string:
<day value="999" date="2011-11-01"/>

Data to extract from:
- value

Values to return:
- 999

Note: the value can be of different length from 1 digit to thousands

Thanks very much in advance for the time you take to review this question

fg
 20111206-EE-extractBetweenQuotes.xlsx
fredericgilbertAsked:
Who is Participating?
 
slycoderCommented:
I made two functions:

Option Explicit

Public Function GetValue(myVar As String, myRange As String) 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 = Range(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


It should do what you need.
Usage:
=GetValue("value","cell")
20111206-EE-extractBetweenQuotes.xlsm
0
 
Glenn RayExcel VBA DeveloperCommented:
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
0
 
TigerManCommented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
fredericgilbertAuthor Commented:
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.



0
 
fredericgilbertAuthor Commented:
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.
0
 
slycoderCommented:
Sorry for the delay - life got in the way -


Question: can you consider upgrading the function?  - Yes  Working on a solution now.
0
 
slycoderCommented:
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


0
 
slycoderCommented:
Updated spreadsheet attached - for future reference.
20111213-EE-extractBetweenQuotes.xlsm
0
 
fredericgilbertAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.