More text manipulation

  • Thread starter Thread starter TeeSee
  • Start date Start date
T

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
 
TeeSee said:
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
Click to expand...

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
 
Back
Top