Searching for Incorrect City Spellings in a Cell

[Kate Needs Help]

Hi,

This is my first time posting, so please excuse the clumsiness and poor formatting.

In my job, I need to validate data before I can start anything. My biggest problem is that cities are often misspelled or entered in the short-hand way and origin/destination pair will appear twice because of the misspelling. In the example below, I might apply a different price to the same o/d pair below because I did not catch the FT instead of Fort. That is a problem.

[TABLE="width: 307"]
<tbody>[TR]
[TD]Origin[/TD]
[TD]State[/TD]
[TD]Destination[/TD]
[TD]State[/TD]
[TD]Volume[/TD]
[/TR]
[TR]
[TD]Fort Worth[/TD]
[TD]TX[/TD]
[TD]Albany[/TD]
[TD]GA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]FT Worth[/TD]
[TD]TX[/TD]
[TD]Albany[/TD]
[TD]GA[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

I have a zip code function that will search zip codes for me, but that will still pull a zip code for cities like "ST Louis" instead of "Saint Louis" when I need everything spelled out. I made a function to find the issues in a cell and mark them for me. =IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"n ","e ","s ","w ","ft ","mt ","mnt ","st "},B2)))))>0,"1",""). This will pull a "1" if at least one of those search criteria is met.

This works well for clear issues like "FT Worth", but this function will not work for some cities like North Little Rock because it will see the "e" on the end of "Little" and pull it as an error. Don't even get me started when the whole city is short hand like "E St Louis" instead of "East Saint Louis".

So my question is, is there some way that I can search a cell in a column for all the errors above (there are more but that is all I can think of right now) and not run into what I will call the "Little Rock" issue? I want to some how search for the first value to be that letter and then a space, so it will find short hand. Does that make sense?

Thanks to anyone who can help.

 

[inactiveuserps07]

Kate, keeping in mind that I don't know your entire setup or what you're trying to do, and therefore based solely on the one issue you've presented here, you can control for the "Little Rock" issue by adding a leading space before the single-letter entries (and probably the "st" as well, to ignore things like "Forest" or "West") as well as the search cell. Here's the adapted formula:

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({" n "," e "," s "," w ","ft ","mt ","mnt "," st "}," "&B2)))))>0,"1","")