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

 

[rdw72777]

Maybe Find/Replace has limits, btu can you work around it. Maybe insert a column next to your original address column, and do a formula like:

=IF(RIGHT(A1,1)="W",A1&"est",A1)

Formula assumes your address is in column A.

 

[laban]

Thanks so much Ryan.

What would be the function for doing it borth for east and west - all in the same column, as well as retaining the correct info in the new column for addresses that did not contain an E or W?

I'm trying to write that all into one function and having trouble.

 

[shg]

Maybe ...

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

 

[laban]

Thanks SHG,

I'm sure this is probably just my lack of understanding, but when I enter that forumal, it just inserts the formula as text into the cell. What am I doing wrong? The first cell I'm pulling my address data from is F2, so I entered:

<TABLE style="WIDTH: 130pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=173><COLGROUP><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6326" width=173><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 130pt; HEIGHT: 11.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=15 width=173>= F2 & IFERROR(LOOKUP(RIGHT(F2, 2), {" E"," N"," S"," W"}, {"ast","orth","outh","est"}), "")</TD></TR></TBODY></TABLE>

Again, all the help is much appreciated.

 

[shg]

Format the cell in which the formula appears as something other than TEXT.

If the formula is referring to cells that are formatted as Text, format the cell where the formula appears as something other than General (e.g., Number).

 

[laban]

Huh, well that allowed me to enter the formula, but the result is that "est" is attached to the end of every entry. Bizarre! What
's going wrong?

 

[mikerickson]

Perhaps =TRIM(SUBSTITUE(A1&" ","W ","West"))

or =SUBSTITUTE(SUBSTITUE(A1&"~","W~","West"),"~","")

 

[laban]

Thanks, Mike, but I think thats going to give me the same error as the Find/Replace. Seriously, why isn't there an anti-wildcard!

 

[mikerickson]

Have you tried
=SUBSTITUTE(SUBSTITUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUE(A1&"~"," W~"," West")," E~"," East")," N~"," North")," S~"," South"),"~","")

The SUBSTITUTE(A1&"~"," W~",..)

insures that only the last W will be effected. Unless there are existing ~ in the string, in which case, you could use CHAR(5), or some other obscure character, that won't be in A1.