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
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.
