Searching for Incorrect City Spellings in a Cell

Kate Needs Help

New Member
Joined
Nov 3, 2017
Messages
1
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.

OriginStateDestinationStateVolume
Fort WorthTXAlbanyGA2
FT WorthTXAlbanyGA25

<tbody>
</tbody>

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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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","")
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top