Remvoing numbers from a text field

Hi
Does anyone have some code that will extract the house number from the
street name? There is a space in between the number and the street
name. All the numbers can be varied lengths. The number is the first
entry in the field.

ex: 123 main st
45466 South St

I am looking to get
123
45466

Thanks for any help

Re: Remvoing numbers from a text field

wrote in message
news:a65ac44c-0b51-473f-b35b-b37177ddcc27@8g2000hsu.googlegr oups.com...
> Hi
> Does anyone have some code that will extract the house number from the
> street name? There is a space in between the number and the street
> name. All the numbers can be varied lengths. The number is the first
> entry in the field.
>
> ex: 123 main st
> 45466 South St
>
> I am looking to get
> 123
> 45466
>
> Thanks for any help

Use the InStr function to detect the position of the first space and then
use the result in the Left function to return just the number.
Incidentally, in a normalised system you would have the number stored in its
own field.

Left([MyField],InStr([MyField]," ")-1)

Keith.
www.keithwilby.com

Re: Remvoing numbers from a text field

Smythe32@aol.com wrote:

> Hi
> Does anyone have some code that will extract the house number from the
> street name? There is a space in between the number and the street
> name. All the numbers can be varied lengths. The number is the first
> entry in the field.
>
> ex: 123 main st
> 45466 South St
>
> I am looking to get
> 123
> 45466
>
> Thanks for any help

Left(Address,Instr(Address," ")-1)

I'd only do that if you are positive the address has a space and perhaps
verify it's a number.
If Instr(Address," ") > 0 then
strAddress = Left(Address,Instr(Address," ")-1)
If IsNumeric(strAddress) then Me.Streetnum = strAddress
Endif

Rome
http://www.youtube.com/watch?v=ldk9s76WLsc

Re: Remvoing numbers from a text field

On Apr 8, 11:54=A0am, Salad wrote:
> Smyth...@aol.com wrote:
> > Hi
> > Does anyone have some code that will extract the house number from the
> > street name? =A0There is a space in between the number and the street
> > name. =A0All the numbers can be varied lengths. =A0The number is the fir=
st
> > entry in the field.
>
> > ex: =A0123 main st
> > =A0 =A0 =A0 45466 South St
>
> > I am looking to get
> > 123
> > 45466
>
> > Thanks for any help
>
> Left(Address,Instr(Address," ")-1)
>
> I'd only do that if you are positive the address has a space and perhaps
> verify it's a number.
> =A0 =A0 =A0 =A0 If Instr(Address," ") > 0 then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 strAddress =3D Left(Address,Instr(Address,=
" ")-1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 If IsNumeric(strAddress) then Me.Streetnum=
=3D strAddress
> =A0 =A0 =A0 =A0 Endif
>
> Romehttp://www.youtube.com/watch?v=3Dldk9s76WLsc

Thank you Both. Both worked like a charm!!!

Re: Remvoing numbers from a text field

wrote

> Thank you Both. Both worked like a charm!!!

Apparently you did not encounter it, but be on the alert for addresses where
the number is spelled out, as in, "Twenty-one Financial Boulevard" or "One
Microsoft Way". Those might require some special handling.

Larry Linson
Microsoft Office Access MVP

Re: Remvoing numbers from a text field

On Tue, 8 Apr 2008 16:01:12 +0100, "Keith Wilby"
wrote:

> wrote in message
>news:a65ac44c-0b51-473f-b35b-b37177ddcc27@8g2000hsu.googleg roups.com...
>> Does anyone have some code that will extract the house number from the
>> street name? There is a space in between the number and the street
>> name. All the numbers can be varied lengths. The number is the first
>> entry in the field.
>> ex: 123 main st
>> 45466 South St
>> I am looking to get
>> 123
>> 45466

>Use the InStr function to detect the position of the first space and then
>use the result in the Left function to return just the number.
>Incidentally, in a normalised system you would have the number stored in its
>own field.

Addresses are "special" cases, Keith. Personally, I'm not sure they
*can* be normalized, as there are just so darn many systems out there!
How would you normalize what "Smythe32" posted along with a series of
PO Boxes.
"P.O. Box 1234" for example.

--
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

Re: Remvoing numbers from a text field

"Chuck Grimsby" wrote in message
news:46vnv3tjbru8idmqnnlpuv047vpqiugs2s@4ax.com...
>
> Addresses are "special" cases, Keith. Personally, I'm not sure they
> *can* be normalized, as there are just so darn many systems out there!
> How would you normalize what "Smythe32" posted along with a series of
> PO Boxes.
> "P.O. Box 1234" for example.
>

IMO your example is the first line of the address and the house number is
null.

Regards,
Keith.