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.

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.

About the Author

SRINI S

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