excel vba- extract text between 2 characters

Solution 1:

You can use instr to locate a character within the string (returning the position of '(' for example). You can then use mid to extract a substing, using the positions of '(' and ')'.

Something like (from memory):

dim str as string
dim openPos as integer
dim closePos as integer
dim midBit as string

str = "NUMBER(8,3)"
openPos = instr (str, "(")
closePos = instr (str, ")")
midBit = mid (str, openPos+1, closePos - openPos - 1)

You may want to add error checking in case those characters don't occur in the string.

Solution 2:

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

firstDelPos = InStrRev(textline, “[“) ‘ position of start delimiter
secondDelPos = InStrRev(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