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) 
     'Store the position of the 1st and 2nd delimiter in the String
    Dim iPositionOfFirstDelimiter As Integer, iPositionOfSecondDelimiter As Integer 
     'Store the length of the delimiter
    Dim iLenDelimiter As Integer 
     
     'Deliver nothing if the function doesn't get a single usable parameter
     'otherwise you'd get an error later on
    If Len(sText) = 0 And Len(sDelimiter) = 0 Then 
        GetStringFromQuotation = "" 
        Exit Function 
    End If 
     
    iLenDelimiter = Len(sDelimiter) 
     'Find 1st occurence of delimiter
    iPositionOfFirstDelimiter = InStr(sText, sDelimiter) 
     'Find the 2nd one, starting right behind the first one
    iPositionOfSecondDelimiter = InStr(iPositionOfFirstDelimiter + iLenDelimiter, _ 
    sText, sDelimiter) 
     
     'If there are 2 occurences
    If iPositionOfFirstDelimiter > 0 And iPositionOfSecondDelimiter > 0 Then 
         'Take the part of the string that's right between them
        GetStringFromQuotation = Mid(sText, iPositionOfFirstDelimiter + iLenDelimiter, _ 
        iPositionOfSecondDelimiter - iPositionOfFirstDelimiter - iLenDelimiter) 
    Else 
        GetStringFromQuotation = "" 
    End If 
End Function

How to use:

  1. Open your VBA Editor (Alt + F11)
  2. Take an existing or create a new Module (Insert - Module)
  3. Copy & Paste that code
  4. To use this in VBA just put a call to this function into your code wherever you need it
  5. 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.
  6. 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:

  1. To test the code in VBA use the immediate Window and enter then following:
  2. Debug.Print GetStringFromQuotation("This #is# a test","#")
  3. You should get: is
  4. 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.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express