Avatar of mtrussell
mtrussell asked on

Find string between two characters in a field

How do you in VBA find the string between two characters in a field?  For example I have a field that is xxxxxxxxxxxxxxx(yyyyyy)zzzzzz.   The only constant is the open and close parenthesis in the field (no constant character position in the field).  What I need is to find the string between the two parenthesis 'yyyyyy'.  How do you do this?
Microsoft AccessVB Script

Avatar of undefined
Last Comment
mtrussell

8/22/2022 - Mon
ASKER
mtrussell

I am using the code

=IF(IsError(FIND(")",[dockey])),"",IF(IsError(FIND("(",[dockey])),"",Left(Right([dockey],Len([dockey])-FIND("(",[dockey])),FIND(")",Right([dockey],Len([dockey])-FIND("(",[dockey])))-1)))


and it is kicking out #Name?


[dockey] is the field which has the xxxxxxxxxxxxxxx(yyyyyy)zzzzzz string.
r0bertdenir0

Where are you doing this VBA, Excel or Access?
ASKER
mtrussell

It's an Access Database.  Using VBA in the modules....
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
r0bertdenir0

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dandraka

Take your string and make
myArray = split(myString,"(")
'xxxxxxxxxxxxxxx(yyyyyy)zzzzzz
This will create an array where you have:
myArray(1) = "xxxxxxxxxxxxxxx("
myArray(2) = "yyyyyy)zzzzzz"

Then you only have to do
myParenthesisArray = split(myArray(2), ")")

And you'll have:
myParenthesisArray(1) = "yyyyyy"   'what you are looking for
myNameArray(2) = "zzzzzz"
Rey Obrero (Capricorn1)


test this


dim str as string, vStr as string
vStr="xxxxxxxxxxxxxxx(yyyyyy)zzzzzz"

str=mid(vstr,instr(vStr,"(") + 1, instr(vStr, ")") - 1 - instr(vStr, "("))


Dale Fye

Capricorn1's method is the way that I would do this, but if you are going to use this as a control source, you would be better off using a function, so that you can handle the case where the field you are testing is NULL or doesn't actually contain "(" and ")" in that sequence.

r0bertdenir0 has the right idea, but his function doesn't take into account that, the search field could be null or blank, or that the character that he is using to identify the end of the search string could appear before the one that starts the process.

You need to decide what your business rules are if the string is not formatted the way you expect, and then write a function that will return what you want in those cases.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GRayL

Yet another:

vStr="xxxxxxxxxxxxxxx(yyyyyy)zzzzzz"

? Array(Replace(Replace(vStr,"(","|"),")","|"))(1)
yyyyyy
ASKER
mtrussell

Hi Everyone - thanks for your comments.  I took Robert's suggestion and had worked around it. It makes sense the other solutions but I had already made the changes to the code so I am rewarding him the points