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