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.

Example:

Suppose below is the first line of a text file

extract-string-between-two-delimiters-example-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

VBA-macro-extract-data-betwen-two-delimiters

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.