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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.