Avatar of Svgmassive
Svgmassive
 asked on

Extract text MS ACCESS

how can i extract the text before and after form "D317A" the letters can increase before or after eg. "DEF317AB ETC.ETC.
Microsoft ExcelMicrosoft Access

Avatar of undefined
Last Comment
aikimark

8/22/2022 - Mon
Flyster

Here's one way to do that:

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Flyster
Dale Fye

Will every instance of that field contain that text?  Your example does not

'D317AB' is not contained in 'DEF317AB'
Jeff Darling

Can you add some more examples?

Is the data something like this?

DEF317AB
DGH317AC
DIJ317AD

Assuming that the code always contains "D something 317A something"
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
jerryb30

Just to be clear:
Must have a 'D' in string occurring before '317A'? And only strings before the 'D' and after the '317A'?
So in sample you cited, you want 'B ETC.ETC.'
Jeffrey Coachman

Svgmassive


This is why I always ask for a sample db and:
 "a clear graphical representation of the *Exact* results you are expecting, based on the sample data."

;-)

Jeff
Flyster

Oops! My solution is for Excel, not Access. Must read those titles closer :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

Flyster,
That's a pretty impressive 3D formula either way...
;-)

jeff
Flyster

Jeff

Thanks!

Paul
Gustav Brock

This is the really lazy solution:

strChars = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(strValue, "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", ""), "0", "")

/gustav
Your help has saved me hundreds of hours of internet surfing.
fblack61
aikimark

You could create a function that instantiates a RegExp object and returns a collection or array that contains the strings.

If you go this route, the RegEx pattern is: D(.*)317A(.*)
ASKER CERTIFIED SOLUTION
Patrick Matthews

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.
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
Jeff Darling

I considered RegEx, but I'm still not sure what the exact problem is.  

attached is such an example
eetest1.accdb
aikimark

@Patrick

I thought we wanted the after ("317A") text.  I ran my pattern against the only sample text posted.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.