Is there an anti-wildcard? Find/Replace Character (Only those followed by nothing)

[laban]

I have a large amount of street address in a spreadsheet that need to be standardized. I am looking to change all entrys like: "101 Mainstreet W" to "101 Mainstreet West". When I do a find replace comand for " W" with " West" it will also alter any other word begining with "W". Ex. ("Oak Way" becomes "Oak Westay").

Is there any kind of anti-wildcard key to specify that I only want "W"s were nothing follows. Unfortunately, whoever originally entered the data did not put a space after the W, which would have simplified this.

Thanks,

Laban

 

[shg]

Brain cramp, sorry.

=A1 & IFERROR(INDEX({"ast","orth","outh","est"}, MATCH(RIGHT(A1, 2), {" E"," N"," S"," W"}, 0)), "")

      -----A------ -------B--------
  1   Meadow Ave   Meadow Ave      
  2   Meadow Ave N Meadow Ave North
  3   Meadow Ave E Meadow Ave East 
  4   Meadow Ave S Meadow Ave South
  5   Meadow Ave W Meadow Ave West

 

[mikerickson]

"Why isn't there an anti-wild card?"

Because it isn't needed.

The existing wildcards allow you to specify "string that ends with W" with the expression "*W"

 

[laban]

Exellent, that does the trick. Thanks SHG!

One final question: Is there a way to prevent the conversion when the letter follows another set of charachters? The issue is that some of the addresses have Unit E, APT E, STE E, and are converted to Unit East.

 

[shg]

I'd punt on formulas and use a UDF.

 

[laban]

Thanks everyone.

SHG, I used your formula and then was able to clean up my apt/unit problem with a few find replace commands. Find: "Unit East" Replace: "Unit E"...

Mike, "*W" didn't limit the search to strings ending in W. It pulls up entry's like "Way" and others. Is this a cell formating problem?

 

[shg]

Good job, glad you got it sorted.