Menu
I have a column with some stuff that looks like the following string: V2397(+60)
How do I get the value between the brackets? In this case the +60.
The number (and character) before the brackets is not something standardized and neither the number between the brackets (it can be 100, 10 -10 or even 0...).
790
To extract the text between any characters, use a formula with the MID and FIND functions. The FIND Function locates the parenthesis and the MID Function returns the characters in between them.
The easiest way to extract a substring between two delimiters is to use the text to column feature in Excel, especially if you have multiple delimiters. In this example, use =MID(A2, SEARCH(“-“,A2) + 1, SEARCH(“-“,A2,SEARCH(“-“,A2)+1) – SEARCH(“-“,A2) – 1) in cell B2 and drag it to the entire data range.
Square brackets ( “[ ]” ): Any expression within square brackets [ ] is a character set; if any one of the characters matches the search string, the regex will pass the test return true.
VBA code:
cellValue = "V2397(+60)"
openingParen = instr(cellValue, "(")
closingParen = instr(cellValue, ")")
enclosedValue = mid(cellValue, openingParen+1, closingParen-openingParen-1)
Obviously cellValue should be read from the cell.
Alternatively, if cell A1 has one of these values, then the following formula can be used to extrcat the enclosed value to a different cell:
=Mid(A1, Find("(", A1)+1, Find(")",A1)-Find("(",A1)-1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
