extract digits from addresses

[shank]

Field [Address]
1234 Anywhere St #29
34 Anywhere St #345
1 Anywhere St #1

How can I extract the Street Numbers from the above records?
I only want the prefix numbers, not any trailing numbers.
In the above examples, I would get...
1234
34
1

Thanks

 

[Debra Farnham]

In a query, something like:

StreetNumber: Mid([Address], 1, InStr([Address], " "))

ought to do it.


HTH

Debra

 

[shank]

That works great except for one problem...
I need to limit action to first digits being numbers only.
Some addresses start with characters and I want to omit those.
thanks!

In a query, something like:

StreetNumber: Mid([Address], 1, InStr([Address], " "))

ought to do it.


HTH

Debra

Field [Address]
1234 Anywhere St #29
34 Anywhere St #345
1 Anywhere St #1

How can I extract the Street Numbers from the above records?
I only want the prefix numbers, not any trailing numbers.
In the above examples, I would get...
1234
34
1

Thanks

 

[Debra Farnham]

Can you give an example of some address that might start with characters?
Is the number of characters a constant number or can it vary?

Debra

That works great except for one problem...
I need to limit action to first digits being numbers only.
Some addresses start with characters and I want to omit those.
thanks!

In a query, something like:

StreetNumber: Mid([Address], 1, InStr([Address], " "))

ought to do it.


HTH

Debra

Field [Address]
1234 Anywhere St #29
34 Anywhere St #345
1 Anywhere St #1

How can I extract the Street Numbers from the above records?
I only want the prefix numbers, not any trailing numbers.
In the above examples, I would get...
1234
34
1

Thanks

 

[John Spencer (MVP)]

Omit the characters or omit the entire street number?

The latter is easy. Although if Address doesn't have a space in it, this might error.

StreetNumber: IIF(Address Like "#*",Trim(Left(Address,Instr(1,Address," "))),Null)

If you want to return the number part of
a1234 Some Wierd St
then you will need to do something much more complex and will probably need to
write a custom user function in VBA.

That works great except for one problem...
I need to limit action to first digits being numbers only.
Some addresses start with characters and I want to omit those.
thanks!

In a query, something like:

StreetNumber: Mid([Address], 1, InStr([Address], " "))

ought to do it.


HTH

Debra

Field [Address]
1234 Anywhere St #29
34 Anywhere St #345
1 Anywhere St #1

How can I extract the Street Numbers from the above records?
I only want the prefix numbers, not any trailing numbers.
In the above examples, I would get...
1234
34
1

Thanks

 

[shank]

Samples starting with characters....
N7733 SWAMP ROAD
WC357 HWY CTY RD
PO BOX 1234

thanks

Can you give an example of some address that might start with characters?
Is the number of characters a constant number or can it vary?

Debra

That works great except for one problem...
I need to limit action to first digits being numbers only.
Some addresses start with characters and I want to omit those.
thanks!

In a query, something like:

StreetNumber: Mid([Address], 1, InStr([Address], " "))

ought to do it.


HTH

Debra


Field [Address]
1234 Anywhere St #29
34 Anywhere St #345
1 Anywhere St #1

How can I extract the Street Numbers from the above records?
I only want the prefix numbers, not any trailing numbers.
In the above examples, I would get...
1234
34
1

Thanks

 

[Debra Farnham]

Here goes:

I actually found this code created by Bent S. Lend:

Function Numbers_only (source_field As String) As String
Dim b As String
Dim tmpstr As String
Dim x As Integer
Dim l As Integer


source_field = Trim$(source_field)
l = Len(source_field)
tmpstr = ""


For x = 1 To l
b = Mid$(source_field, x, 1)
If (b >= "0") And (b <= "9") Then tmpstr = tmpstr & b
Next


Numbers_only = tmpstr


End Function


In your query design grid add Numbers_only([Address])

I have tested it and found that the only time it will not work is when you
have numbers in two separate locations in the address (ie. 2460 Main
Street, Unit #6).

If that is an issue, perhaps you can modify the function to suit your needs.

HTH

Debra

Samples starting with characters....
N7733 SWAMP ROAD
WC357 HWY CTY RD
PO BOX 1234

thanks

Can you give an example of some address that might start with characters?
Is the number of characters a constant number or can it vary?

Debra

That works great except for one problem...
I need to limit action to first digits being numbers only.
Some addresses start with characters and I want to omit those.
thanks!

In a query, something like:

StreetNumber: Mid([Address], 1, InStr([Address], " "))

ought to do it.


HTH

Debra


Field [Address]
1234 Anywhere St #29
34 Anywhere St #345
1 Anywhere St #1

How can I extract the Street Numbers from the above records?
I only want the prefix numbers, not any trailing numbers.
In the above examples, I would get...
1234
34
1

Thanks