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>References
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_DESCRIPTION: Replace([COM_APP_LNK_DESCRIPTION],"\([\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_DESCRIPTION], "(" )

[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.

[NeoPa]

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:

I'm sorry if you're just looking for an answer to be done for you. As you'll see from my previous post, that's not really what we're here to offer you. Certainly we'll give examples where appropriate, and sometimes they may even suit your precise requirements.

However, if what you have doesn't help you enough to understand how to proceed then please explain clearly what you currently don't understand. As of now it's not clear to me from what you've posted whether you still have a problem, and if so where. There have been a number of examples posted which look to me like they would be enough to help you do this. Some of which you seem to have ignored completely.

If there's one that you'd prefer to work with but need further clarification on then please post clearly what that is and what help you need.

One tip I can give that may help is that when working within a query and specifying a field using any functions, if you enter them all in lower case then all valid functions will be automatically fixed to the same case as is required for that function. Any that remain in lower case will be unrecognised by the SQL engine processing them and may need to be looked at. For that reason it's very important when posting what you are currently working with that you copy it directly from your work after it's been entered into your query. That gives us clues missing from what you've posted for us in post #13 for instance.

[hvsummer]

@NeoPa: yes sir, your respond is somewhat explain everythings.
next time I'll give indirect code (as an example) and explain rather than an direct answer.

btw, I suggest that if you guy post anything to help them, you guy should explain more detail about the method that we suggest them to follow, classify by SQL/query and VBA code to let them know where to apply/try those code.

because I saw a lot of people seem don't know what happen and can't explain their current situation in technically languages. And an example like this:

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

will make them go deeper into a mist. (even me, did a lot of homework still don't know what that '^\([^(]*\)([^)]*)\([^(]*\)$','' represent for...)

I know they have to do their homework (search and research/study). but not everyone can easily understand computing science in short time.

[goodaan]

What is this lecture?

I've read all answers. I also gave EXACTLY what I am looking for. You all give a string as an example. I am working with a variable string in a field.

Yes I do understand the theory. No I have not found anything that helps me. Either my syntax is wrong or it's not possible to replace the Str with a field. ZMBD said it was not possible with a field.

If I take this answer:

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

and replace strA with my field name, [FIELD], it does not work.

However this was the most helpful answer as it's easier for me to understand and I can see clearly the syntax.

Yes, I can always look it up in the Pears dictionary... so please do not waste any more of your time doing my 'WORK'.

Merry Xmas (I'm working)

p.s. I shall continue removing the text in brackets manually

p.p.s I gave you the code I put in the query expression Builder:

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

[zmbd]

Goodan, I understand you are frustrated; however, Please read thru to the end of this post - it's long; however, certainly not intended to be a lecture.

goodaan I've read all answers. I also gave EXACTLY what I am looking for.

+ In your initial post, all you gave were the strings and the desired outcome.
+ You do not indicate in any way shape or form that you desiring a primarily query based solution.
+ You do not indicate which version of Office/Access you are using (however, in this case, the version doesn't matter)
+ You also do not indicate what level of experience you have with Access.
+ You asked a general question and post#2 and Post#4, and Post#5 provided some general approaches to the solution.

+ Really was not until after hvsummer's post (Post#13) that you indicated that you wanted a query based approach.

goodaanYou all give a string as an example. I am working with a variable string in a field.

+ The values in your fields are strings/text; thus, we gave you some options to handle strings. The values in your field would be passed to these functions by reference to the field name. Access understands in a query or record-set that the value in the field for the current record is to be passed to the function(s)

goodaanZMBD said it was not possible with a field.

+1st, I apologize for misleading you. I envisioned a solution based on VBA, namely that you could create a user-defined-function based on one of the options in post#2, and use that either in your select query or as an update query against your data.

I did notice in post#7 and post#8 that you were using a calculated field/control; and I should have more clearly stated in Post#9 that the implementation might be easier in VBA, especially if one is wanting to make a permanent change to the original data as it's a little less fiddly when creating an update-query. Let me clear, this can be directly accomplished via an update-query without any VBA whatsoever... I suggest creating the SELECT query first to make sure that the records that may be affected are properly returned and that the new value is properly formatted, it is then fairly easy to switch this to an update-action-query.

goodaanand replace strA with my field name, [FIELD], it does not work.

+ Telling us only that it doesn't work, doesn't help us to help you. Are you getting an error in the field such as "#Error" or "#Name" or nothing at all?

goodaan

No I have not found anything that helps me. Either my syntax is wrong or it's not possible to replace the Str with a field

p.s. I shall continue removing the text in brackets manually

p.p.s I gave you the code I put in the query expression Builder:

Twice here you indicate that nothing is working for you at this point - most likely it's a syntax error; however, then you post another set of code, did this work for you? If not then I suggest the following:

(Of course, you need to replace the [Test] with your field name. This was the field I used to verify that the following worked:

rmvprnths: Left([Test],(InStr([Test],"(")-1)) & Mid([Test],(InStr([Test],")")+1))

This is the simplest query based implementation of Option two in post#2; however, instead of Right() I've used the Mid() string functions.
To use,
Create a new query
Add your table
Any fields you want shown to the grid
In a blank column in the grid, copy and paste the above in to the "field row"
> This may leave extra spaces in the resulting string as it is cutting exactly on the "(" and ")" characters. Thus something like"
"aaa.-.bbbb.(.NNNN.).CCCC"
Will return "aaa.-.bbbb..CCCC" where the periods represent spaces. Using one of the Trim Functions remove the undesired spaces.
> If you run this without changing the "Test" to your table's field name you will be prompted to enter a "Test" value.
> The query field name will be shown as [rmvprnths] of course you might want to change that to something else?! :)
> This also avoids the regular-expressions, that although are very powerful, I think in this case, may be a bit esoteric for your application.

:)

goodaanso please do not waste any more of your time doing my 'WORK'.

Please take a deep-breath here... you have had the attention of four people here that have tried to help, advise, suggest possible solutions. Each of us with a slightly different approach in both potential solutions and in communication styles. Text based communications have a serious flaw in that we do not get to hear the intonation nor see the "body language" of the other person. Quite simply, in this format, without those queues/clues, it is too easy to take offense where none is intended. Even the silly emoticons don't always provide enough.

goodaanMerry Xmas (I'm working)

\\\Merry Christmas//
(As am I - production labs run 7/24/365)

[NeoPa]

hvsummer:
And an example like this:

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

Regular expressions are certainly not easy for people to deal with who are not comfortable with computer science, as you say. That's why, on this particular occasion and although a link had already been posted to another thread which included the required explanations, I included the actual code required. That's the closest I can come to what will actually work for goodaan in the scenario he's outlined. It was theoretical, so I don't guarantee it, but I felt the subject was complicated enough that expecting someone with no relevant experience to work their way through what was there would be a little too much.

hvsummer:
(even me, did a lot of homework still don't know what that '^\([^(]*\)([^)]*)\([^(]*\)$','' represent for...)

Let me try to explain as clearly as I can in case of interest. Goodaan hasn't posted any reply to this yet so I have no idea if they are interested in pursuing it, but as stated earlier, many more people read these threads than just those involved posting in them.

First let me state that it's important to review the code in the linked thread. Without copying that specific code into your database as instructed none of what follows will work or make sense. RegExReplace() takes three parameters :

1. The original string value. This can be a field reference and can also handle a value of Null if passed.
2. The pattern to match against the first parameter. This can contain regular expressions.
3. The pattern to replace the original with. This controls which value is returned by the function. My value suggestion is clearly wrong here. I just noticed this as I was trying to formulate an explanation. It should read '\1\2' instead.

^ - This starts the searching at the beginning of a line. In this case I would expect it to start at the beginning of the value passed.
\( & \) - mark the beginnings and ends of marked groups that can be referenced in the replacement expressions.
[...] - marks a single character expression that is any one of the characters within the brackets.
[^...] - marks a single character expression that is any character other than those within the brackets after the ^. Thus [^(] refers to any character except (.
* - means to repeat the previous expression as many times as it can, which may be none. No repeats still means at least one occurrence of course, to match the original specification.
$ - Marks the end of the value.
, - Parameter separator.
'' - Empty string as replacement string. As mentioned earlier - this is not a correct value. It should be '\1\2'.

Thus :
Start at the beginning; Continue while characters <> '(' & mark string traversed for later as \1 (First such block); Process past '('; Continue while characters <> ')'; Process past ')'; Continue while characters <> '(' & mark string traversed for later as \2 (Second such block).

That last bit should probably not contain anything that cares whether or not it's an '(' and matching the first such '(' is irrelevant. So, my recommendation should actually be :

COM_NEW_DESCRIPTION: RegExReplace([COM_APP_LNK_DESCRIPTION],'^\([^(]*\)[^)]*)\(.*\)$','\1\2')

. - Means one of any character.

NB. I used the field names you used in post #7.

[NeoPa]

@Goodaan.
Please read my PM about your interactions on this site (Private Message Inbox).

[hvsummer]

merry Xmas guys

@goodaan:

if you apply that code into an query that have multi table joined together, you have to add table's name before the field's name

[table1].[Field'sName]

that what I guess you're missing.

btw, you should give some error information as zmbd suggestion to let us know what exactly wrong there ^^

[NeoPa]

hvsummer:
if you apply that code into an query that have multi table joined together, you have to add table's name before the field's name

While that may be true in some cases that is actually quite rare. It is only required when referring to a field name that is the same for more than one of the record sources of a query. Otherwise, Access is clever enough to determine exactly which field you're referring to.

Bear it in mind though, as it can be a problem in such circumstances.

[hvsummer]

Yup, you're right, some case it could be true, some case it was wrong.

to be sure, I think he should add table's name, it can prevent thing go wrong like you guy alway advice to normalize data :D

[goodaan]

Bingo! I could kick myself as the answer is in fact very simply. I am new to Access and do not know the InStr function. I should have thought how to do this in BO or Excel. The Trim works, as well as -2 instead of -1 from the left position.

I suppose I thought everyone uses queries in Access since I am not a programmer - I'll try to be clearer next time. Thank you zmbd for the answer I was looking for. Thanks to all others who joined in.

Like for email, I say nothing if I have nothing to say - this does not mean I was ignoring the answers. I just found most unnecessarily complicated and I did not see how to implement them in the query :-)

[NeoPa]

goodaan:
Like for email, I say nothing if I have nothing to say - this does not mean I was ignoring the answers.

That's fair enough. I would just advise that most experts will perceive that as inappropriate though, for the OP (Original Poster or instigator of the thread). I don't imagine I need to explain why.

I'm very pleased you managed to find your solution anyway.

Merry Christmas.

[NeoPa]

FYI:
I played around with the RegEx code a little, and was certainly frustrated by the paucity of information, particularly appropriate information, in the linked article. Eventually I found the following to work :

COM_NEW_DESCRIPTION: RegExReplace([COM_APP_LNK_DESCRIPTION],'^([^(]*)[^)]*\)(.*)$','$1$2')

NB. This will typically leave a double-space in the middle of the result if the data was in the format :

Before:    XXXXX (YYYYY) ZZZZZ
After:     XXXXX  ZZZZZ

This again uses the code found in Macro to format data in MS Access Form.