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
Who is Participating?

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.

Commented:

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

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

Excel 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
Commented:
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
Author Commented:
Hi all,

thanks for your quick replies. It is much appreciated.

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
Author 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
Commented:
Sorry for the delay - life got in the way -

Question: can you consider upgrading the function?  - Yes  Working on a solution now.
0
Commented:
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
Commented:
Updated spreadsheet attached - for future reference.
20111213-EE-extractBetweenQuotes.xlsm
0
Author 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.