Replacing Misspelled sub-string in varying full string

I have a situation where a word (in this case "Restaurant") is misspelled in a list of about 78,000 location names. The location names are in one column, and the [misspelled] word "Restaurant" is anywhere between the first word of the string, to the last, with any amount of alphanumeric/symbol characters between. For example:

Alice's Restaurant
Alli's Restaurant & Bar
Alexis Restaurant of Waukesha
Amigo's Mexican Restaurant #2

I want to replace any misspellings of the word with correct, but since the list is so long, and the way the word is misspelled varies so much, going through manually is entirely too time-consuming.

Some of the variations I've seen so far are Resta, Restau, Restaur, Restuara, etc...

Is there a way to search and replace cells that contain the text in any location of the string? Specifically, where ever there is JUST "Restau", replace with "Restaurant", regardless of where it is in the string? This way, trailing text is not deleted or manipulated, i.e. "Alice's Restaur and Bar" will change to "Alice's Restaurant and Bar".

There are many misspelled words in this list, but the most ubiquitous is Restaurant, so any help with this would be extremely helpful. Thanks.

Spell checker under Review tab?

Try this to replace anything that starts with "Rest" with "Restaurant"

=REPLACE(A1,SEARCH("Rest",A1),SEARCH(" ",MID(A1,SEARCH("Rest",A1),255))-1,"Restaurant")

adjust A1 to suit your original string

Originally Posted by Special-K

Spell checker under Review tab?

Restaurant wasn't offered when checking Restau and other trimmings...

You could use a UDF.

Please Login or Register  to view this content.

The D:E range at right has the correct spelling in the top row, and misspellings below for all the words you want to correct. Col E changes Astoria to BStoria just as an example. You can make the range as wide as needed.

The formula in B2 and down is

=WordSubstitute(A2, $D$1:$E$9)

The UDF is

Please Login or Register  to view this content.

Originally Posted by freerdj

I intentionally misspelled my cell to "Cozumel Mexican Restarant" and applied the function to it and it returned a value error.

Sorry...didn't account for it being the last word...

Try:

=REPLACE(A1,SEARCH("Rest",A1),SEARCH(" ",MID(A1,SEARCH("Rest",A1),255)&" ")-1,"Restaurant")

Originally Posted by freerdj

The misspell is not always the last word; will this function apply to other variations as well?

Yes, it will work as long as the part string "Rest" or "rest" is there.

You are welcome...

...Note that this is not 100% foolproof...

If you have a spelling like Ristorante then it will not get fixed...

If this might be the case, then after you applied the 1st phase, copy that new column and paste special, selecting Values, over the original or over itself (remember to maintain a backup in case of disaster)... Then apply formula like

=IF(ISNUMBER(SEARCH("Rist",A1)),REPLACE(A1,SEARCH("Rist",A1),SEARCH(" ",MID(A1,SEARCH("Rist",A1),255)&" ")-1,"Restaurant"),A1)

this will leave the correct ones alone and fix the ones with Rist in it...

You can repeat this again and again to get any subtleties you may encounter...

Maybe shg's UDF is something to look into.. I am sure you can expand the D:E columns to include more fixes and other words....

so in D1, you have Restaurant and below all possible mispellings, in E1, you have Grill and below all possible misspellings, in F1 you have Lounge and below... well, you get it..

and then adjust Formula: =ReSpell(A2, $D$1:$E$9) to the full range of words to check for and corrections..

and I'm always discovering new ones

Just add them to the list, and make the range taller than necessary (whole columns, perhaps). It quits in each column when it gets to a blank cell.