Seperating text and numbers in a string

I am not an Access jock, but if Access supports regular expressions then you can partition the string on a regular expression.  In this case the (standard format) regex would be:

^(.*)\s*[0-9]

Select allOpen in new window

This will partition everything before the first digit into capture group 1, dropping trailing spaces (if any exist.)

I’ll give it a try first thing tomorrow. Thanks

This tiny function will pull the string:

Public Function LeftString(ByVal Value As String) As String

    Dim Result      As String
    Dim Position    As Long
   
    For Position = 1 To Len(Value)
        If Not IsNumeric(Mid(Value, Position, 1)) Then
            Result = Left(Value, Position)
        Else
            Exit For
        End If
    Next
   
    LeftString = Result
   
End Function

Select allOpen in new window

So, in your query it could be:

Select *, LeftString(Nz([YourAddressField])) As Building From YourTable

Select allOpen in new window

Gustav: Your code separates text and numeric values just as you said placing it in it's own field and that part is great but how do I now remove that same text from the Address field?

with no space between than

Is there any character between them?  Often there is a carriage return or line feed character that doesn't show up in a textbox or datasheet cell.

Everything worked exactly like you said it would. Thank you!

You are welcome!