Is this possible?

E

Ernest Lai

Hi all,

I have a field that will contain the house number. So it can be either
1,2,3 or 1a, 25a etc...

Is it possible to write a macro or some kind of script that would move any
non numerical characters from this field into the next field.

Example:

If A1 had 25a, then i would like the script to keep 25 in A1, and move "a"
to B1
 
A

Arvin Meyer [MVP]

The problem with your request is that datasheets from databases are not
spreadsheets. There is no A1 and B1.

You can pick out just numbers, but what would you do with:

123 W. 26th St.
 
E

Ernest Lai

Sorry I forgot to mention that the data comes to me in spreadsheet format.
And yes!!! those addresses are a huge problem. Initially I identified that
the trouble addresses would be

hyphenated addresses (11-12 etc.)
alphanumeric (11a 123b etc.)
Or something like yours: Flat 1 The High Street 999 Avenue Road

at the mo im trying to aim for a 97-99% hit of addresses that will be able
to be imported into our system. What we may do is have an error pool, and
enter those address manually into the system.

I managed to find a lil forumla that is entered as an array that splits the
Alpha numeric fields. I just change the True and False around depending on
which part of the field i want.
=MID(A6,MATCH(FALSE,ISNUMBER(1*MID(A6,ROW($1:$8),1)),0),COUNT(1*MID(A6,ROW($1:$8),1)))
 
E

Ernest Lai

argh you cant edit posts :(

A1 B1 was just an example, i just needed the non numerical part of the 11a
seperated from the alpha. Either in excel or access. But it seems i've
cracked that. The next problem is turning 11-12 to something useful,
probably dropping the -12 somehow. (dont suppose you cant think of a way to
do this ?)

Thanks very much for your time to answer my question, sorry it was a bit
vague.

Ernest
 
A

Arvin Meyer [MVP]

Typically, addresses are stored in a single field, the exception being when
there is a second address line, it is sometimes added to a second field.
The reason for a single field is that decomposing the data into multiple
fields generally will leave both of the fields incomprehensible. For
instance:

John M. Smith makes sense in that each of the fields directly describe the
person entity. However, 123 East 11th St also is an attribute of the person
entity, but 123 is not, nor is East or 11th or St.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
J

JTreks

I have inherited a database where the street number is in a separate field
from the street name (as are the suffixes (Rd, Dr, etc.) and directions (E W
N S).

I'm having issues trying to sort.... Order By SName, SSuffix, SNumber
results in my thousands addresses being before my hundreds, don't even ask
where 87 1/2 winds up and then there are the A's, B's, etc. I'm thinking
there some nightmarish algorithm I could create.... but.... is there an
easier way? I've tried putting the numbers in the same field as the name but
wind up with similar issues....

Any help here?
 
J

John W. Vinson

I have inherited a database where the street number is in a separate field
from the street name (as are the suffixes (Rd, Dr, etc.) and directions (E W
N S).
Click to expand...

That's a typical and good design.
I'm having issues trying to sort.... Order By SName, SSuffix, SNumber
results in my thousands addresses being before my hundreds, don't even ask
where 87 1/2 winds up and then there are the A's, B's, etc. I'm thinking
there some nightmarish algorithm I could create.... but.... is there an
easier way? I've tried putting the numbers in the same field as the name but
wind up with similar issues....

Any help here?
Click to expand...

Order by Val([SNumber]) instead of by SNumber.

John W. Vinson [MVP]
 
Top