Access Excel remove text in brackets

This Access Excel VBA tutorial explains how to remove text in brackets using custom Function.

You may also want to read:

Change text color in brackets

Extract text in brackets

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,"(",")"),"[","]")

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *