How to Find and Replace only if critera is at the end of the string?

[windcloud2]

I have a huge mailing list and I have a macro to find and replace certain critera, the goal is to seperate these addresses into 2 parts.
the street address in ADDRESS1 and the apt/suites in ADDRESS2, If it doesnt have a apt/suite # then it should be blank.

What I want to do is find "* st" and as long as "st" is the end of the string then replace with ""

for example:

A1) 526 West 26th Street, Suite 920
A2) 526 W. 26th St. Ste 920
A3) 253 W. 23th St
A4) 235 34th st Apt 3
A5) 343 lafayette st

so that after I run the macro I have in my ADDRESS2 the following:

B1)Suite 920
B2)Ste 920
B3)
B4)Apt 3
B5)

If I do a simple Find and replace of "*st" I come up with the problem of replacing parts of A2 and
im left with "e 920" which is not what I want.
I want to be able to create multiple search criterias and not just those that have " st" at the end of the
string.
Does anyone know when using a Find and Replace if there is a special character to indicate that its the end of the string?

A3) 253 W. 23th St
that way i can Find " st(Special Character to indicate end of string)" replace with ""

thanks.

[Dennis7849]

How many addresses do you have to do this too?

Does your data contain COMPLETE addresses? Street, city, state, zip? If you have complete addresses, there may be a better way to do this work.

[windcloud2]

can be up to 10000 addresses, there is a field for city, state, zip as well. But only the Address field needs to be seperated into 2 parts. The City,State, Zip are all correct and do not need to be altered.

[windcloud2]

Another criteria I want to do search for is "*East", Find "*East" and if its at the end of the string then Replace with "", resulting in a blank field.
If "*East" is not at the end of the string then leave it alone.

Example:
A6)1001 G Street NW, Suite 900 East
A7)Independence Mall East
A8)750 Park Ave NE, apt 39 west

Results should look like this:
B6)Suite 900 East
B7)
B8)apt 39 West

[Kelshaer]

in the find and repalce dialog check the "Match Entire cell content option"

[windcloud2]

how would you indicate that in a macro?

[Kelshaer]

Please Login or Register  to view this content.

[windcloud2]

works great thanks!

[Kelshaer]

Good. Please mark the thread as SOLVED

[windcloud2]

Also I would like to find "*??th Fl" and replace with "??th Fl". The goal is to remove all text behind the floor number.

A1) 633 Broadway, 44th Floor
A2) 27 street and 3rd Avenue 34th Floor
A3) 245 Fifth Avenue 25th floor
A4) 350 Seventh Aveue, 19th floor Apt 4
A5)350 Seventh Avenue, 19th floor suite 3

Results should be
B1)44th Floor
B2)34th Floor
B3)25th floor
B4)19th floor Apt 4
B5)19th floor suite 3

Rather then create a macro that Finds every possible floor number ( 11th floor, 12th floor, 13th floor)
is there a way to say find any instance from 11th floor to 99th floor and replace with the same floor?
lets say it finds the 25th floor I want it to delete all textbehind the 25th floor.