|
|
|
|
|
|
Multiple Apps
|
Get String between quotation marks
|
|
Ease of Use
|
Easy
|
Version tested with
|
97, 2003
|
Submitted by:
|
Steiner
|
Description:
|
This function extracts a part of a String which is enclosed by a given pair of delimiters. You can use it directly from VBA or as an userdefined function in Excel.
|
Discussion:
|
Assume you have lot's of entries which look like that: =T("<value>") . This function helps you to extract just the <value> part of each entry. This function does not use the split function to achieve this, so it will even work with Office97. It will return the first match it finds as a String, so no need to go through an array.
|
Code:
|
instructions for use
|
Option Explicit
Public Function GetStringFromQuotation(sText As String, sDelimiter As String)
Dim iPositionOfFirstDelimiter As Integer, iPositionOfSecondDelimiter As Integer
Dim iLenDelimiter As Integer
If Len(sText) = 0 And Len(sDelimiter) = 0 Then
GetStringFromQuotation = ""
Exit Function
End If
iLenDelimiter = Len(sDelimiter)
iPositionOfFirstDelimiter = InStr(sText, sDelimiter)
iPositionOfSecondDelimiter = InStr(iPositionOfFirstDelimiter + iLenDelimiter, _
sText, sDelimiter)
If iPositionOfFirstDelimiter > 0 And iPositionOfSecondDelimiter > 0 Then
GetStringFromQuotation = Mid(sText, iPositionOfFirstDelimiter + iLenDelimiter, _
iPositionOfSecondDelimiter - iPositionOfFirstDelimiter - iLenDelimiter)
Else
GetStringFromQuotation = ""
End If
End Function
|
How to use:
|
- Open your VBA Editor (Alt + F11)
- Take an existing or create a new Module (Insert - Module)
- Copy & Paste that code
- To use this in VBA just put a call to this function into your code wherever you need it
- To use it as userdefined function in Excel, simply enter the formula: =GetStringFromQuotation(A1;B1) where A1 is the cell with the text and B1 the cell with the delimiter.
- One thing to note: if you want to pass " as delimeter directly to the formula, you'll have to enter """" (one " opens the String to pass, the second and third tell VBA you really want a " as part of a String and not to close the String, the 4th then closes the String)
|
Test the code:
|
- To test the code in VBA use the immediate Window and enter then following:
- Debug.Print GetStringFromQuotation("This #is# a test","#")
- You should get: is
- Or use the attached sample file to see how it works as userdefined formula / VBA call.
|
Sample File:
|
GetStringFromQuotation.zip 9.27KB
|
Approved by mdmackillop
|
This entry has been viewed 69 times.
|
|