put space between number and letter

[michael.boucher]

I have an address field of 60000+ that I am trying to clean. One thing
I am trying to do is take every apartment number and make it more
standard. Here is my question:

How do I take a record like "NORTHGATE APT5C" and another record like
"NORTHGATE APT5C WESTWOOD AVE" and make them look like:

"NORTHGATE APT 5C" and "NORTHGATE APT5 C WESTWOOD AVE"

I have many different examples, and they don't all have Apt in them. I
figured out how to find them all, using:

Like "*#[a-z]* or Like "*[a-z]#*"

but I don't know how to add a space between them. this is basically an
advanced find/replace, and I'm pretty sure I can do it, but I'm not
sure how to create the update query.

Thanks,
Michael

[Rich P]

Hi Michael,

Try something like this: Copy a subset of your data to a local table.
Without knowing all the details of your data I am only offering a
general solution/idea that you can experiment with.
UPDATE tblSampleSet SET Address = Replace(Address,"Apt", "Apt ");

What this will do is to take every Address field in your dataset and if
the row field contains Aptxx that value will be replaced with Apt xx.
If this works on your test table, then you can apply it to your main
table.

hth

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[michael.boucher]

On Apr 23, 12:50 pm, Rich P <[e-mail]:

Hi Michael,

Try something like this: Copy a subset of your data to a local table.
Without knowing all the details of your data I am only offering a
general solution/idea that you can experiment with.

UPDATE tblSampleSet SET Address = Replace(Address,"Apt", "Apt ");

What this will do is to take every Address field in your dataset and if
the row field contains Aptxx that value will be replaced with Apt xx.
If this works on your test table, then you can apply it to your main
table.

hth

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Thanks Rich. I was hoping for a more wide-sweeping solution, but this
will work well for most of the situations.

[Larry Linson]

Do you really want the apartment number formatted differently if it is
followed by a street name, or was that a typo? In my experience, a street
name is normally preceded by a number -- is that not the case with your
addresses? Is it the case with _some_ of them? If there is an apartment
number, is it always in the form APTnX where n is a number and X is
alphabetic? Can n be more than one digit? Can X be more than one letter?

And given that you give only two examples, in which the only reformatting
appears to be of the representation of the apartment number, then refer to
many other examples, some of which do not have an apartment number, it is
very difficult for me to understand exactly what it is that you want to
accomplish. Could you clarify? If you can, perhaps someone can make a
useful suggestion.

The problem most of us have with trying to standardize addresses is that
real-world addresses are not "standard" -- they come in many forms, with
variations that are acceptable in many uses (e.g., delivering letters or
packages). What often results is a long list of conditions to determine the
original format, and a long set of special cases to re-format them.

Larry Linson
Microsoft Access MVP
<mi*************@gmail.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...

>I have an address field of 60000+ that I am trying to clean. One thing
I am trying to do is take every apartment number and make it more
standard. Here is my question:

How do I take a record like "NORTHGATE APT5C" and another record like
"NORTHGATE APT5C WESTWOOD AVE" and make them look like:

"NORTHGATE APT 5C" and "NORTHGATE APT5 C WESTWOOD AVE"

I have many different examples, and they don't all have Apt in them. I
figured out how to find them all, using:

Like "*#[a-z]* or Like "*[a-z]#*"

but I don't know how to add a space between them. this is basically an
advanced find/replace, and I'm pretty sure I can do it, but I'm not
sure how to create the update query.

Thanks,
Michael

[michael.boucher]

On Apr 23, 4:23 pm, "Larry Linson" <[e-mail]:

Do you really want the apartment number formatted differently if it is
followed by a street name, or was that a typo? In my experience, a street
name is normally preceded by a number -- is that not the case with your
addresses? Is it the case with _some_ of them? If there is an apartment
number, is it always in the form APTnX where n is a number and X is
alphabetic? Can n be more than one digit? Can X be more than one letter?

And given that you give only two examples, in which the only reformatting
appears to be of the representation of the apartment number, then refer to
many other examples, some of which do not have an apartment number, it is
very difficult for me to understand exactly what it is that you want to
accomplish. Could you clarify? If you can, perhaps someone can make a
useful suggestion.

The problem most of us have with trying to standardize addresses is that
real-world addresses are not "standard" -- they come in many forms, with
variations that are acceptable in many uses (e.g., delivering letters or
packages). What often results is a long list of conditions to determine the
original format, and a long set of special cases to re-format them.

Larry Linson
Microsoft Access MVP

<[e-mail] in message

news:11**********************@y80g2000hsf.googlegr oups.com...

I have an address field of 60000+ that I am trying to clean. One thing
I am trying to do is take every apartment number and make it more
standard. Here is my question:

How do I take a record like "NORTHGATE APT5C" and another record like
"NORTHGATE APT5C WESTWOOD AVE" and make them look like:

"NORTHGATE APT 5C" and "NORTHGATE APT5 C WESTWOOD AVE"

I have many different examples, and they don't all have Apt in them. I
figured out how to find them all, using:

Like "*#[a-z]* or Like "*[a-z]#*"

but I don't know how to add a space between them. this is basically an
advanced find/replace, and I'm pretty sure I can do it, but I'm not
sure how to create the update query.

Thanks,
Michael

Thanks for your help. I was afraid it would take many conditions, but
I guess that is what I have to do.

First, it was a typo in my original post, but unfortunately for all
the addresses, there are many different formats, from various
organizations. I am trying to match addresses across these
organizations, and I'd like to be able to map it as well. Hence the
desire to standardize these records.

I was very excited when I was able to identify all of the entries that
contained numbers and letters next each other without spaces simply
putting [a-z]# using Ctr-F. It was able to highlight exactly what I
wanted to locate, but I couldn't replicate that in the query box. If I
can mimic the Ctr-F function and copy and paste each value it
highlights into another field, would be able to fix it decently well.

Basically, I want to separate all the numbers and letters that placed
together without spaces. If you want more detail, let me know.

Thanks,
Michael