We have many people entering addresses into the main database and by
the time we get them the addresses are not in any standard format,
i.e, Street, St, St., RR 1, Rt. 1, Rt 1, etc. I'm creating a macro
that will examine known problems and change to the new standard but
have come across a problem when the search string ends with characters
that need to be changed. Here's an example,

501 East Station St as entered should convert to

501 E. Station St. East to E. & Street to St.

The problem is when checking for St and replaceing with St. will also
change Station to St.ation

Selection.Replace What:=" St", Replacement:=" St.", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

This will only happen when the text is at the end of the string as I'm
checking for leading and following spaces on the other scans.

Hope this makes sense and someone can provide a work around.

thx

After serious thinking Jack Deuce wrote :
We have many people entering addresses into the main database and by
the time we get them the addresses are not in any standard format,
i.e, Street, St, St., RR 1, Rt. 1, Rt 1, etc. I'm creating a macro
that will examine known problems and change to the new standard but
have come across a problem when the search string ends with characters
that need to be changed. Here's an example,

501 East Station St as entered should convert to

501 E. Station St. East to E. & Street to St.

The problem is when checking for St and replaceing with St. will also
change Station to St.ation

Selection.Replace What:=" St", Replacement:=" St.", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

This will only happen when the text is at the end of the string as I'm
checking for leading and following spaces on the other scans.

Hope this makes sense and someone can provide a work around.

thx

You should *always* use the full text of the string being replaced so
the function operates on only that text. So...

Selection.Replace What:=" Street", Replacement:=" St.", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

*Note* that it isn't necessary to include any parameters that contain
default values.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Jack Deuce wrote...
....
501 East Station St *as entered should convert to

501 E. Station St. * * *East to E. & Street to St.

The problem is when checking for St and replaceing with St. will also
change Station to St.ation
....

The Range class's Replace method is an overly blunt tool. You'd have
much finer control reading cell contents into a VBA string variable,
making the changes to that string variable, then writing the modified
string back to the cell's value.

However, you have bigger potential headaches ahead. How would you
distinguish St meaning Street from St meaning Saint? E.g.,

123 St James St

If you had any Washington, DC addresses, you'd need to accommodate
addresses like

111 Massachusetts Ave NW

but there could be St rather than Ave, so there should be St not at
the end which should become St. and other instances which should.
Further complicating things, how about distinguishing Ave above with
Ave Maria Boulevard (there are a few in the US)? Or some of my
favorites from around where I live: Street Road, Boulevard Way, Circle
Court, Tyson Circle.

If your goal is eliminating text strings which differ but refer to the
same address, then uniformity is the goal rather than brevity. Fully
spelling out every word in the database would work best. If you want
to save on ink when printing addresses, use a specific purpose filter
at that stage to convert East to E., Street to St., etc. just before
printing (though the USPS would be perfectly fine with no periods, so
you could save even more ink dispensing when them). It's much easier
to abbreviate text than expand it.

Back to the question of how to transform text. Add spaces at the
beginning and end of each address to make it easier to check entire
words, then search for " St " or " East " rather than "St" or "East".

Gary,

That's what I was attempting to do. To standardize St to St.
" St" is the full text. I even added the preceeding space in front of
St but the problem is REPLACE replaces " Station St" as " St.ation
St." I was hoping there is a way to tell REPLACE to only FIND/REPLACE,
under certain circumstances, where the value is at the end of text.
There would be way too many parmateres to enter if I had to enter each
unique value. I may as well change them manually.


On Mon, 27 Feb 2012 11:48:11 -0500, GS wrote:

After serious thinking Jack Deuce wrote :
We have many people entering addresses into the main database and by
the time we get them the addresses are not in any standard format,
i.e, Street, St, St., RR 1, Rt. 1, Rt 1, etc. I'm creating a macro
that will examine known problems and change to the new standard but
have come across a problem when the search string ends with characters
that need to be changed. Here's an example,

501 East Station St as entered should convert to

501 E. Station St. East to E. & Street to St.

The problem is when checking for St and replaceing with St. will also
change Station to St.ation

Selection.Replace What:=" St", Replacement:=" St.", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

This will only happen when the text is at the end of the string as I'm
checking for leading and following spaces on the other scans.

Hope this makes sense and someone can provide a work around.

thx

You should *always* use the full text of the string being replaced so
the function operates on only that text. So...

Selection.Replace What:=" Street", Replacement:=" St.", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

*Note* that it isn't necessary to include any parameters that contain
default values.

On Feb 27, 2:20*pm, Jack Deuce
wrote:
Gary,

That's what I was attempting to do. *To standardize St to St.
" St" is the full text. *I even added the preceeding space in front of
St but the problem is REPLACE replaces " Station St" as " St.ation
St." I was hoping there is a way to tell REPLACE to only FIND/REPLACE,
under certain circumstances, where the value is at the end of text.
There would be way too many parmateres to enter if I had to enter each
unique value. *I may as well change them manually.







On Mon, 27 Feb 2012 11:48:11 -0500, GS wrote:
After serious thinking Jack Deuce wrote :
We have many people entering addresses into the main database and by
the time we get them the addresses are not in any standard format,
i.e, Street, St, St., RR 1, Rt. 1, Rt 1, etc. *I'm creating a macro
that will examine known problems and change to the new standard but
have come across a problem when the search string ends with characters
that need to be changed. *Here's an example,

501 East Station St *as entered should convert to

501 E. Station St. * * *East to E. & Street to St.

The problem is when checking for St and replaceing with St. will also
change Station to St.ation

Selection.Replace What:=" St", Replacement:=" St.", LookAt:=xlPart, _
* * * * SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
* * * * ReplaceFormat:=False

This will only happen when the text is at the end of the string as I'm
checking for leading and following spaces on the other scans.

Hope this makes sense and someone can provide a work around.

thx

You should *always* use the full text of the string being replaced so
the function operates on only that text. So...

*Selection.Replace What:=" Street", Replacement:=" St.", _
* * * * *LookAt:=xlWhole, SearchOrder:=xlByRows, _
* * * * *MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

*Note* that it isn't necessary to include any parameters that contain
default values.

How many names total. If you want to send your file to dguillett1
@gmail.com I'll take a stab

Harlan makes some very good points. I can only suggest you use the
'Whole words' option so it doesn't find 'part' occurances. I'm thinking
that maybe you want to implement Excel's Replace function.

In the case of 123 St James St:

The 1st occurance of "St" occurs as " St " and so is clearly
different than " St"! So if your criteria is to replace " St " with "
St." then you're okay. Otherwise, you need to do some text
manipulation[s] to get it right.

I'd go with Harlan's suggestion to read the cell content into a string
variable and then work it from there. I only say this because I do a
lot of string replacement coding and so have not only a fair bit of
experience with this but also I've developed a number of text utilities
I use regularly for various string manipulations. A lot of the work I
do involves abbreviations and so I'll give you a few pointers if you
choose to go the string variable route.

1a. Multiple word strings are usually delimited with the space
character.

1b. Multiple line strings are usually delimited by a LineFeed,
CarriageReturn, or a combo of both (CarriageReturn LineFeed).

2a. You can use delimiters to disect a string variable into individual
words as array elements with VB's Split() function.

2b. You can use Split() multiple lines into single lines that you can
then process as individual multiple word strings.

3. The position of abbreviated 'words' gives an indication as to its
context within what you're doing with the string as a whole.

4. Splitting strings into individual words allows you to better
analize how to approach what you want to do with the string.

For example...

123 St James St

The context is 'address' and so there's only a given number of
'reasonable' possibilities:

Dim vText As Variant
vText = Split("123 St James St", " ")
..returns a zero-based 1D 4 element array as follows:
LBound(vText) = 0
UBound(vText) = 3
vText(0) = "123"
vText(1) = "St"
vText(2) = "James"
vText(3) = "St"

..that you can process based on the UBound of the array...
Select Case UBound(vText)
Case = 3
'This is a 3-part street name
' ie: "123 E James St", "123 N James St", "123 S James St"
' ie: "123 W James St", "123 St James St"
' context here is 1 of "East,North,South,West"
' so.. Len(vText(1)) = 1 and so will match the 1st letter of one
' of the four values in the context string.

'OR this is a 2-part street number and 2-part street name
' ie: "123 B James St"
' so.. vText(1) will not match the 1st letter of any of the four
' values in the context string. In this case, vText(1) should be
' appended to vText(0) because its Len() doesn't reasonably
' suggest it's a typical abbreviation...
' vText(0) = vText(0) & vText(1)

End Select

Needless to say, the process can get unwieldy! For example, how could
you handle "1A - 123 St james St", where an apartment number prefixes
the street number. Clue: the hyphen qualifies as a typical context
identifier and so you end up with a 5 element array. That's going to
present a challenge if you don't establish a protocol for handling such
addresses in your code. For example "1A-123 St James St" is the same
address configured differently.

*Note*
It's always a good idea to either restrict the input via Data
Validation OR train users to follow input messages/cues/instructions.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi All

If I may offer.

Why not add an extra column and have that column record the street via
Data Validation where the street/road format is predefined so that users
can only select from that list.

This would then eliminate the need for complicated VB coding and the
potential Saint / Street conflict issue.

You could then, if you choose to, use vba to update the address value to
include Address&" "&Street.

Or

A UserForm for entering required details ( still using a Combo/List
Box/DataValidation for street selection that updates the underlying cells.

HTH
Mick.