how do you remove all text before first number in a text field

[rankenory]

I haven't found a reference to this problem anywhere on the web, much less a solution. (I'm hoping there's a solution for doing this in query design view, or, if not, SQL view, but I'm certainly open to finally learning something about module creation, if that's the best way around this. I've never written a module before, so I'm not even sure how to call for it in the query.)

The problem: I have a field that starts with unwanted text before the first number appears. I only want to keep the part starting with the first number.

I realize that a "number" in this text field is seen as text to Access, and I know that Chr(48) through Chr(57) is how Access would recognize these ASCII number characters. I am not aware of another way to represent all of these characters with something like a # sign in query design (tried and failed), so I'm guessing I'll have to reference them using Chr.

The field: The field is called "Location Address", and it contains street addresses that follow text relating to the type of facility at the address.

Examples of data records in the field:
Fitten Hall 855 McMillian Street, NW
Baker Building 939 Dalney Street, NW
Barnes and Noble 48 Fifth Street, NW

How I want the addresses to read in my query result (from which I will make a new table):
855 McMillian Street, NW
939 Dalney Street, NW
48 Fifth Street, NW

I tried to use InStr, Len and Right functions to identify the position of the first number and remove everything before it, but I only know how to ask for one number in the InStr, for instance:
[code]: Address starting with number 8: Right([Location Address],Len([Location Address])-InStr(4,[Location Address],"8")+1)

This does return Fitten Residence Hall 855 McMillian Street, NW as 855 McMillan Street, but does not work with 939 Dalney Street. Worse yet, 48 Fifth Street becomes 8 Fifth Street!



I would love to see a solution to this. My need to resolve this isn't great, but at this point I just want to know how it could be solved, because I spent hours researching this with no solution.

Thanks

[pbaldy]

The only way that comes to mind is a custom function that accepts the entire string as an input and outputs the desired string. It would step through the characters looking for a number (For/Next loop). Once it finds one, you use the position with InStr() to return the desired string.

[rankenory]

Can anyone provide an answer involving specific formulas or code that would work with this type of problem?

The only way that comes to mind is a custom function that accepts the entire string as an input and outputs the desired string. It would step through the characters looking for a number (For/Next loop). Once it finds one, you use the position with InStr() to return the desired string.

[rankenory]

Looking at Paul's reply, I guess it just suprises me that this problem would require a solution that complex, since we know that the set of characters we are interested in have ASCII representations that can be referred to. I was interested to know if there are formulas or combinations of formulas that would do that.

Can anyone provide an answer involving specific formulas or code that would work with this type of problem?