This Access Excel VBA tutorial explains how to remove text in brackets using custom Function.
You may also want to read:
Access Excel remove text in brackets
I was inspired to create this post because the HR system I am using (Workday) is very annoying, some data fields are actually a combination of two different fields, the additional data field is displayed in brackets in the prefix. For example, an employee has a English name followed by local name inside brackets in the suffix, but I only want the English name. Therefore I create a custom Function so that I can use it in Microsoft Access and Microsoft Excel.
VBA Code – remove text in brackets
In this custom Function, you can specify the exact type of open bracket and end bracket you want to apply. It searches for multiple sets of brackets, not just one set.
Public Function wRemoveBrackets(myString, stStr, edStr) tempString = myString For i = 1 To Len(myString) If InStr(tempString, stStr) > 0 And InStr(myString, edStr) > 0 And InStr(tempString, edStr) > InStr(tempString, stStr) Then tempString = Trim(Left(tempString, InStr(tempString, stStr) - 1) & Right(tempString, Len(tempString) - InStr(tempString, edStr))) End If Next i wRemoveBrackets = tempString End Function
Function Syntax
wRemoveBrackets(myString, stStr, edStr)
| Argument | Explanation |
| myString | The text or Range that contains the brackets contents to remove |
| stStr | Define the open bracket |
| edStr | Define the close bracket |
Example
The results are self-explanatory. This custom Function removes all characters inside specific open / close brackets, brackets inclusive.
| Value | Formula | Result | Explanation |
| 123 (456) 789 (012) 345 | =removeBrackets(A2,”(“,”)”) | 123 789 345 | Remove characters inside two sets of brackets |
| 123()(456)789 | =removeBrackets(A3,”(“,”)”) | 123789 | Remove brackets even if there is no contents inside |
| 123[456]789(012) | =removeBrackets(A4,”[“,”]”) | 123789(012) | Remove characters inside square brackets as specified in the argument |
| (45678(9012)) | =removeBrackets(A5,”(“,”)”) | ) | Remove characters inside the complete pair of ( ) |
Note that you can apply the function more than once in order to remove more than 1 set of brackets. For example, to remove square brackets and round brackets in text in A1
=removeBrackets(removeBrackets(A1,"(",")"),"[","]")
