Extract text MS ACCESS

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

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

'D317AB' is not contained in 'DEF317AB'

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"

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

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

Oops! My solution is for Excel, not Access. Must read those titles closer :)

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

jeff

Jeff

Thanks!

Paul

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

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(.*)

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

attached is such an example
eetest1.accdb

@Patrick

I thought we wanted the after ("317A") text.  I ran my pattern against the only sample text posted.