VBA macro to extract string between two delimiters in a text file

Excel VBA macro to extract string between two delimiters in a text file. This macro extracts string between two delimiters within a string even it contains multiple delimiters.

VBA code:

Sub extractStringBetTwoDels()
inputFile = Application.GetOpenFilename()
Open inputFile For Input As #1 'Opening the file to read its data
Line Input #1, textline 'reading the first text line
firstDelPos = InStr(textline, "[") ' position of start delimiter
secondDelPos = InStr(textline, "]") ' position of end delimiter
stringBwDels = Mid(textline, firstDelPos + 1, secondDelPos - firstDelPos - 1) ' extract the string    between two delimiters
MsgBox (stringBwDels) ' message shows string between two delimiters
Close #1 ' closing the file
End Sub

 Note: If you get any compilation error, highlighting any special character, then delete it and                      manually type.


Suppose below is the first line of a text file


1. Run the above macro

2. It will pop up a file choose/browse window

3. select the text file

4. A message box displays showing the data between two delimiters i.e., hello in above text file


If the string contains multiple delimiters and you want to extract string between specific delimiters, then the above macro needs little bit customization.

Case 1:

If the string is “Value of A is [1.0234] and Value of B is [3.2345]”

If you want to extract the value of B i.e., 3.2345, then replace 6 and 7 lines in the above VBA macro with below lines

6. firstDelPos = InStrRev(textline, “[“) ‘ position of start delimiter
7. secondDelPos = InStrRev(textline, “]”) ‘ position of end delimiter

Note: In the above macro, delimiter considered is “[” and “]”. If there is any other delimiter in the string, the just replace “[” and “]” delimiters with those in the above macro.

About the Author


A passionate blogger. Love to share solutions and best practices on wordpress hosting, issues and fixes, excel VBA macros and other apps