Removing text from the middle of a string - in Access

[goodaan]

I have string values as follows:
RM - B08 - (1510) EXTERNAL
IRMM - D01 - (M21305) MANAGEMENT
RM - B03 - (S-1120) TRAINING
IES - H02 - (A24007) AIR
IET - F06 - (A13102 / A13111 / A13206) ENERGY TECHNOLOGY

How can I remove the middle code surrounded by brackets - using Access?

[zmbd]

goodaan
Welcome to Bytes.com.
We don't normally start out by posting code snippets here at bytes.com unless it's something really difficult or unique instead, we try to provide guidance to the solution as each user's experience and situation is unique.

In your case a few suggestions as starting points, because you have a variable string:

One approach is to:

+ Two calls to InStr Function, once to find the open parenthesis, once to find the close parenthesis.
+ Now that you have position if the desired text, you can calculate the length of the text and then use the midstr function to copy that text to a variable.
+ Replace Function to replace with a string of your choice.

Another method,

Once again, use the InStr function to return the positions of the parenthesis, the len function along with left function and right function to return the strings on either side of the parenthesis group and concatenate the strings.

Yet a third method,

Using a for-next loop (or even a do-until-loop same concept however one would have a little different logic and I see two methods for implementing the DoUntilLoop) and the LEN(), using the Mid(), step thru the original string, concatenate the value to a holding string until the character returned is the open parenthesis then skip concatenating it and subsequent characters until the close parenthesis then concatenate the value of the remainder to the holding string finally returning the holding string

These are just the first three methods "off the top of my head"

If you will design your code, review the basic troubleshooting steps (read here), and then - if you are still not obtaining the desired results - post back your script with a description of what is happening we can help you fine-tune the script.

also just a bit of semantics; however, it helps if we all use the same names for some things:

( ) are Parentheses
[ ] are Brackets
{ } are Braces

[goodaan]

thank you for your suggestions. I will try the 1st & 2nd method. And I will keep in mind the names (I usually say brackets for parentheses, square brackets for brackets). Regards

[zmbd]

Just thought of another method
Using the split function
Split on the Open Parentheses
This will give you one array:
A()=[RM - B08 - ][1510) EXTERNAL]
Then feed A(1) to the split on the close Parentheses again to get:
B()=[1510][ EXTERNAL]
Now concatenate A(0) and B(1):
"RM - B08 - EXTERNAL"
You may have to use one of the trim functions to remove extra spaces

[jforbes]

You could also use Regex to strip it out. This is similar https://bytes.com/topic/access/answe...ms-access-form

A pattern like this \([\s\S]*?\) could remove a set of Parentheses and the text between them.

[zmbd]

Using jforbes suggestion will require that a reference to the Regex library be set or late-binding be implemented:

for early binding"

In the VBA-Editor
Tools>Reference s
Scroll down to "Microsoft VBScript Regular Exressions 5.5" and checkmark it.
The version number (5.5) may be different, of course, one should normally use the newest revision.

The reason I do not routinely suggest the Regex for these types of questions is mainly due to the fact that this is not a default library nor built in to VBA by default; thus, if the database is used on a system where this library is not referenced it can fail unless late binding is used.

[goodaan]

How do I use your pattern? The following gives an error:

COM_NEW_DESCRIP TION: Replace([COM_APP_LNK_DES CRIPTION],"\([\s\S]*?\)","")

[goodaan]

I'm afraid I'm new to Access. I tried this to find the first parentheses but the syntax is no good:

FIND_PARENTH: InStr(1, [COM_APP_LNK_DES CRIPTION], "(" )

[zmbd]

goodaan,
this is vba code, not for a calculated field/control.

I will send you a copy of resources and tutorials, please check your Bytes.com inbox

[NeoPa]

Hi.

I don't know if you read the thread linked in post #5, but if you use the function there in post #20 (I'm sure you could also use the one by JForbes in post #12 too but I only know my own one.) I expect you can call it with :

RegExReplace([FieldName],'^\([^(]*\)([^)]*)\([^(]*\)$','')

[hvsummer]

zzz, you guy bring the problem too far ==

to remove string inside () in RM - B08 - (1510) EXTERNAL <-- call strA
you can use this code

left(strA,len(strA) - instr(1, strA, "(",0)) & right( strA, len(strA) - instr(1, strA, ")", 0))

[zmbd]

zzz, you guy bring the problem too far ==

to remove string inside () in RM - B08 - (1510) EXTERNAL <-- call strA
you can use this code

left(strA,len(strA) - instr(1, strA, "(",0)) & right( strA, len(strA) - instr(1, strA, ")", 0))

1) this is the exact method I gave in my post for option two in post#2:

[ZMBD]:
Another method,
Once again, use the InStr function to return the positions of the parenthesis, the len function along with left function and right function to return the strings on either side of the parenthesis group and concatenate the strings.

Therefor, your post really adds very little to the thread that hasn't already been offered.

[goodaan]

Yes this is the sort of answer I am looking for. However I am using the expression Builder in a query, and I have a field name instead of strA:

TO_DESCRIPTION: left([FROM_DESCRIPTION],len([FROM_DESCRIPTION]) - instr(1, [FROM_DESCRIPTION], "(",0)) + right( [FROM_DESCRIPTION], len([FROM_DESCRIPTION]) - instr(1, [FROM_DESCRIPTION], ")", 0))

I suppose this cannog be done directly in a query? If I have to use code, I do not even know how to declare a variable - coming back to Access after 20 years and right now just designing queries with REPLACE.

[hvsummer]

@goodaan: Yes, you can do it directly in SQL/Query because that function work on SQL-languages

to explain what code do, I'll simplize it for you.

right(string, number of string) and left() function give back part of string from Field's value.
if number of string = string length, it give you full string.
Len(string) give you the number of characters in string
instr(looking position, string to be searched, string use as criteria, compare method) that enable you to know where the character/string used as criteria found in "string to be searched"

so combine those function, that like this:
right( "ABC", len("ABC") - instr(1, "ABC", "B", 0))
mean it'll give back string from the right of "ABC" that having number of string equal len("ABC") = 3 minus instr(1,"ABC"," B",0) = 2 (character "B" is in 2nd position) --> right("ABC", 3-2) --> right("ABC", 1) = "C"

[NeoPa]

hvsummer:
@zmbd: I gave exactly code to fullfill his request == not the indirect way like you.

Please read the site guidelines on posting responses to questions.

Most people can easily provide code for responses, but we aren't here to do people's work for them. The site is there, as are most of the volunteer members, in order to advise people on how to do things. That's a lot harder, but far more valuable. It's more valuable particularly because so many thousands and millions of other users need help with similar questions that don't have the same specific answer. Furthermore, we are careful not to encourage posters to feel that they can come here and take undue advantage of the willingness of our volunteers to help them. Helping is one thing. Doing it for them because they're unwilling to learn how to themselves, is quite another.

Thus, simply posting code as an answer is not acceptable, and is a disservice to other volunteer members. I see you've later tried to explain your code, which is certainly more acceptable.

Each member that posts a question is perfectly at liberty to say if they don't understand what's being explained to them. That doesn't mean they can expect to be spoonfed code.