Spliting Colums

[Robert Sykes]

Hello

There is a column in one of my table that contains address information. Is
there a way that I can split the column into 2 columns, one getting the unit
number (123) and the second gettting the street name (Fake St.)?

Thanks in advance.


--
Notice of Confidentiality:
This transmission contains information that may be confidential and that may
also be privileged. Unless you are the intended recipient of the message
(or authorized to receive it for the intended recipient), you may not copy,
forward, or otherwise use it, or disclose its contents to anyone else. If
you have received this transmission in error, please notify us immediately
and delete it from your system.

 

[Jamie Richards]

Hi Robert,

See the following at Dev Asish's site:

http://www.mvps.org/access/strings/str0001.htm

Jamie

 

[Robert Sykes]

Thanks for the help, but I'm not to sure about access, Db is not my thing,
it was just something dropped into my lap by management.

That example is for last, first name, how would I adapt it for an a format
such as ### Fake St? Also I'd only want to pull the numbers off the front of
the value, not at the end? ie 123 Fake St Apt. 42?

Please help this poor fool who's in over his head!

 

[John Vinson]

Thanks for the help, but I'm not to sure about access, Db is not my thing,
it was just something dropped into my lap by management.

That example is for last, first name, how would I adapt it for an a format
such as ### Fake St? Also I'd only want to pull the numbers off the front of
the value, not at the end? ie 123 Fake St Apt. 42?

What results would you expect from the following addresses:

Ballacraine
1812 1/2 Cherokee St
69 Wendell St. #4
25-5 Sylvain Court
301 Maple Ave. Apt. B


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Nurick]

Hi John,

What results would you expect from the following addresses:

Ballacraine
1812 1/2 Cherokee St
69 Wendell St. #4
25-5 Sylvain Court
301 Maple Ave. Apt. B

Not to mention a couple of non-US examples:
Flat 22, 84 Vicarage Drive
3/71 Thomas Street
and of course
12A Superstition Drive

 

[Robert Sykes]

Hey guys, thanks for this, I don't have to worry about non-US style
addresses for now, and it doesn't have to be completely perfect (nothing
ever is). I can see from your examples that some problems will arise from
it, but I'd rather manually correct a few hundred records then have to
retype the entire thing. really all that would suffice is something that
would put all numerical data into one table and all alphabetical data in
another, the rest I could sort out manually.