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

<pre><code class="code-1 nolinks" id="code-20-43051587-1"><span>^(.*)\s*[0-9]</span>
</code></pre>
<br />
This will partition everything before the first digit into capture group 1, dropping trailing spaces (if any exist.)
</div>


<div>
I’ll give it a try first thing tomorrow. Thanks
</div>


<div>
This tiny function will pull the string:<br><br><pre><code class="code-10 nolinks" id="code-20-43051739-1"><span>Public Function LeftString(ByVal Value As String) As String</span>
<span></span>
<span>&nbsp; &nbsp; Dim Result &nbsp; &nbsp; &nbsp;As String</span>
<span>&nbsp; &nbsp; Dim Position &nbsp; &nbsp;As Long</span>
<span>&nbsp; &nbsp;</span>
<span>&nbsp; &nbsp; For Position = 1 To Len(Value)</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; If Not IsNumeric(Mid(Value, Position, 1)) Then</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Result = Left(Value, Position)</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; Else</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Exit For</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; End If</span>
<span>&nbsp; &nbsp; Next</span>
<span>&nbsp; &nbsp;</span>
<span>&nbsp; &nbsp; LeftString = Result</span>
<span>&nbsp; &nbsp;</span>
<span>End Function</span>
</code></pre>So, in your query it could be:<br><br><pre><code class="code-1 nolinks" id="code-20-43051739-2"><span>Select *, LeftString(Nz([YourAddressField])) As Building From YourTable</span>
</code></pre><br>
</div>


Principal Software Engineer

Dr. Klahn

<div>
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?
</div>


SpaceCoastLife

<div>
<blockquote>
with no space between than
</blockquote>
Is there any character between them? &nbsp;Often there is a carriage return or line feed character that doesn't show up in a textbox or datasheet cell.
</div>


<div>
Everything worked exactly like you said it would. Thank you!
</div>


<div>
<div class="content wysiwyg-content">
I'm looking for help separating text from numbers in an Access string. Example: &quot;BURLINGTON TOWERS1000 BURLINGTON AVE N 603&quot;<br />
<br />
I need everything left of 1000 in it's own field.<br />
<br />
There are a few thousand records with every combination of text and numbers in this table with no space between than.
</div>
</div>


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.

Seperating text and numbers in a string

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.