We help IT Professionals succeed at work.

Seperating text and numbers in a string

SpaceCoastLife
on
Medium Priority
37 Views
Last Modified: 2020-03-23
I'm looking for help separating text from numbers in an Access string. Example: "BURLINGTON TOWERS1000 BURLINGTON AVE N 603"

I need everything left of 1000 in it's own field.

There are a few thousand records with every combination of text and numbers in this table with no space between than.
Comment
Watch Question

Dr. KlahnPrincipal Software Engineer
CERTIFIED EXPERT

Commented:
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]

Open in new window


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

Author

Commented:
I’ll give it a try first thing tomorrow. Thanks
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
So, in your query it could be:

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

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
That you can do this way:

Select
    *,
    LeftString(Nz([YourAddressField])) As Building,
    Mid(Nz([YourAddressField]), 1 + Len(LeftString(Nz([YourAddressField])))) As Address
From
    YourTable



CERTIFIED EXPERT
Top Expert 2014

Commented:
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.

Author

Commented:
Everything worked exactly like you said it would. Thank you!
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!