Extract Everything to the right of the first numeric

[jo15765]

We have an excel workbook that is imported into access, but the Address field is always sent with some garbage data before the actual address. An example is

LYTZ Mason Mitch - Stand Alone1234 North Main St. Novia, MI 33333

Is it possible in a query to extract only the

1234 North Main St. Novia, MI 33333

[Welshgasman]

Perhaps, if you created your own function.?

However what is the 'garbage' has numbers in it?
What happens if the address does not have numbers in it?

I would be looking to get the data corrected BEFORE trying to import?

[jo15765]

I may have to create my own function.

Unfortunately it doesn't appear that I'll be able to get the list in a good usable format when received (I've been trying for 3 months). I'll keep checking for how to locate a number in a string, and figure out how to parse from there. I'm sure LEFT or Mid or some combo can handle it, just have to find that int

[Welshgasman]

The simplest method would be to check each character for an ascii value of x to y where x = ascii(0) and y ascii(9). So loop for 1 to len(data) using Mid(data,loop,1)
As soon as you find a value in that range, that gives you your starting position for a MID() and you can exit the loop.

That will not work however for any of the caveats I mentioned previously?

[jo15765]

Thanks for that tip!

The address will always have numerics in it.

[Micron]

You can use LIKE with "#" to determine if a character is a number. So in your UDF, which a query can call,

loop from i = 1 to Len(string)
Use Mid i to test for "#" and if it is True,
use Mid(str,i) to return the rest.
exit function

Would you like to take a crack at it, or should I post a sample?

[Micron]

I'm facing a reboot for Windows update so I will lose this notepad text (and I don't want to save it). Easier to just dump it here as a follow up to my last post.
Against my nature, but here's a fish...

Function testNum(str As String) As String
Dim i As Integer

For i = 1 To Len(str)
   If Mid(str, i, 1) Like "#" Then
      test = Mid(str, i)
      Exit Function
   End If
Next
End Function

RESULTS
?testnum ("LYTZ Mason Mitch - Stand Alone1234 North Main St. Novia, MI 33333")
1234 North Main St. Novia, MI 33333

[Welshgasman]

Against my nature, but here's a fish...

Had to add some dummy text to post