Thread: 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.

Code:

       ------------A------------ --------------B--------------- C -----D----- ---E---
   1   Location Title            Correction                       Restaurant  BStoria
   2   Astoria Restaruant Inc    BStoria Restaurant Inc           Resta       Astoria
   3   Beauvais Caribbean Restau Beauvais Caribbean Restaurant    Restaraunt         
   4   Bedford Springs Resort    Bedford Springs Resort           Restaruant         
   5   Beijing Restaruant        Beijing Restaurant               Restau             
   6   Bella Luna Pizzeria Resta Bella Luna Pizzeria Restaurant   Restauarant        
   7   Bernardo's Mexican Resta  Bernardo's Mexican Restaurant    Restauarnt         
   8   Bershes Family Restauarnt Bershes Family Restaurant        Restaur            
   9   Billy Joes Restaraunt     Billy Joes Restaurant            Restaurnte         
  10   Blue Jeans Lounge & Resta Blue Jeans Lounge & Restaurant                      
  11   Boothbay Harbor Inn Resta Boothbay Harbor Inn Restaurant

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

Code:

Function WordSubstitute(ByVal sInp As String, r As Range)
    ' The top row of r contains replacement words for the words below
    
    Dim rCol        As Range
    Dim cell        As Range
    Dim sFind       As String
    Dim sRepl       As String

    sInp = " " & WorksheetFunction.Trim(sInp) & " "

    For Each rCol In r.Offset(1).Resize(r.Rows.Count - 1).Columns
        sRepl = " " & rCol.Cells(0).Text & " "

        For Each cell In rCol.Cells
            sFind = " " & cell.Text & " "
            If Len(Trim$(sFind)) = 0 Then Exit For

            If InStr(1, sInp, sFind, vbTextCompare) Then
                sInp = Replace$(sInp, sFind, sRepl, , , vbTextCompare)
                Exit For
            End If
        Next cell
    Next rCol

    WordSubstitute = Mid$(sInp, 2, Len(sInp) - 2)
End Function

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..