More text manipulation

[TeeSee]

Within a recordset each record has data within brackets e.g. (12345)
How can I delete the contents and the brackets? The string "(12345)"
can be both in the middle or the end of the total field string.

Thanks

 

[Stuart McCall]

Within a recordset each record has data within brackets e.g. (12345)
How can I delete the contents and the brackets? The string "(12345)"
can be both in the middle or the end of the total field string.

Thanks

If there are no other values in brackets (and never will be), the following
code should do it:

Dim p1 As Long, p2 As Long
Dim Result As String

'Find the leftmost bracket
p1 = Instr(1, MyField, "(")

'Find the rightmost bracket
p2 = InstrRev(MyField, ")")

'Store all to left of p1 in Result
Result = Left(MyField, p1 - 1)

'Store all to right of p1 in Result
Result = Result & Mid(MyField, p2 + 1)

Debug.Print Result