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