5.5 Inconsistent City Spellings
The same city is typed in many ways. Aurangabad was officially renamed Chhatrapati Sambhaji Nagar; our standard spelling in this book is Sambhaji Nagar.
Before
| Order ID | City (raw) |
|---|---|
| BLK-3101 | pune |
| BLK-3102 | PUNE |
| BLK-3103 | Pune City |
| AMN-3104 | Aurangabad |
| AMN-3105 | Chh. Sambhajinagar |
| BLK-3106 | Nasik |
| BLK-3107 | Kolhapur |
| BLK-3108 | Sholapur |
After
| Order ID | City |
|---|---|
| BLK-3101 | Pune |
| BLK-3102 | Pune |
| BLK-3103 | Pune |
| AMN-3104 | Sambhaji Nagar |
| AMN-3105 | Sambhaji Nagar |
| BLK-3106 | Nashik |
| BLK-3107 | Kolhapur |
| BLK-3108 | Solapur |
Method 1 – Find & Replace (few variants)
Steps in Excel
- Select the City column › Home › Editing › Find & Select › Replace (Ctrl + H).
- Find
Aurangabad› Replace withSambhaji Nagar› Options » › tick Match entire cell contents › Replace All. - Repeat for
Nasik→Nashik,Sholapur→Solapur,Pune City→Pune. - Finish with
=PROPER(TRIM(B2))for case and spaces.
Method 2 – mapping table (many variants, repeatable)
Create a Table CityMap on a Lists sheet:
| Raw (lower case, trimmed) | Standard |
|---|---|
| pune | Pune |
| pune city | Pune |
| aurangabad | Sambhaji Nagar |
| chh. sambhajinagar | Sambhaji Nagar |
| chhatrapati sambhaji nagar | Sambhaji Nagar |
| nasik | Nashik |
| sholapur | Solapur |
| kolhapur | Kolhapur |
Then in the data:
=XLOOKUP(LOWER(TRIM(B2)), CityMap[Raw], CityMap[Standard], "CHECK: "&B2)
Anything new shows "CHECK: …" so you can add it to the map. (Older Excel: =IFNA(VLOOKUP(LOWER(TRIM(B2)),CityMap,2,FALSE),"CHECK: "&B2).)
Ravindra Bagale's Tip
If Match entire cell contents is not ticked in Find & Replace, many students end up with a mess: while replacing "Pune", "Pune City" becomes "Pune City City". Always tick it. For data that comes every day, a mapping table is best – once it's built, a new spelling can be fixed just by adding one row.
Ravindra Bagale's Tip – मराठी
Find & Replace मध्ये Match entire cell contents tick न केल्यास बऱ्याच students चा गोंधळ होतो – "Pune" replace करताना "Pune City" चं "Pune City City" होतं. Tick नक्की करा. रोज येणाऱ्या data साठी mapping table best – एकदा बनवला की नवीन spelling फक्त एक row add करून सोडवता येते.
Ravindra Bagale's Tip – हिंदी
Find & Replace में Match entire cell contents tick न करने पर बहुत से students के साथ गड़बड़ होती है – "Pune" replace करते समय "Pune City" बन जाता है "Pune City City". इसे ज़रूर tick करो. रोज़ आने वाले data के लिए mapping table best है – एक बार बन गई तो नई spelling सिर्फ़ एक row जोड़कर ठीक हो जाती है.
Practice task
Build a CityMap for at least 12 spellings of our six cities (include Aurangabad, Nasik, Sholapur, Kolhapur with a trailing space). Apply it and make sure no "CHECK:" rows remain.