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:

 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