5.5 Inconsistent City Spellings
Fix city spellings with a mapping table with example — in 30 seconds
What: A small mapping table lists every wrong spelling and the one correct city name. A lookup replaces each raw value.
Why: Pune typed four ways becomes four rows in a PivotTable. One mapping table fixes this month and every month after.
Tiny example: " pune " and "Poona" both become Pune. "Kolapur" is not in the map, so it shows "CHECK: Kolapur".
Fix city spellings with a mapping table with example — 30 सेकंदात
काय: छोट्या mapping table मध्ये प्रत्येक चुकीचं spelling आणि एकच बरोबर city नाव असतं. Lookup प्रत्येक raw value बदलतो.
का: Pune चार प्रकारे type केलं तर PivotTable मध्ये चार rows होतात. एक mapping table हा महिना आणि पुढचे सगळे महिने सांभाळतो.
छोटं उदाहरण: " pune " आणि "Poona" दोन्ही Pune होतात. "Kolapur" map मध्ये नाही, म्हणून "CHECK: Kolapur" दिसतं.
Fix city spellings with a mapping table with example — 30 सेकंड में
क्या: छोटी mapping table में हर गलत spelling और एक सही city नाम होता है. Lookup हर raw value को बदल देता है.
क्यों: Pune चार तरह से type हो तो PivotTable में चार rows बनती हैं. एक mapping table यह महीना और आगे के सारे महीने संभालती है.
छोटा उदाहरण: " pune " और "Poona" दोनों Pune बनते हैं. "Kolapur" map में नहीं है, इसलिए "CHECK: Kolapur" दिखता है.
First look at the data (before → after):
Expected result — Raw → clean city
| B: Raw city | C: Clean city |
|---|---|
| " pune " | Pune |
| "Poona" | Pune |
| "nasik" | Nashik |
| "Sambhajinagar" | Sambhaji Nagar |
| "Kolapur" | CHECK: Kolapur |
About this data: Teaching sample for a fictional shop “CityBasket Retail”. Figures and names (e.g. Neha Shah) are made up for this lesson on inconsistent city spellings.
Before table and formula as text
Before — Mapping table CityMap (lower case raw)
| Raw | Standard |
|---|---|
| pune | Pune |
| poona | Pune |
| nasik | Nashik |
| nashik | Nashik |
| sambhajinagar | Sambhaji Nagar |
About this data: Demo table only — not real customer or company data. Built to practise inconsistent city spellings.
Formula / click path
C2: =XLOOKUP(LOWER(TRIM(B2)), CityMap[Raw], CityMap[Standard], "CHECK: "&B2)
Do it step by step:
- Make a table named
CityMapwith columns Raw (lower case) and Standard. - In C2, type the formula shown above.
- LOWER and TRIM clean the raw text before the lookup.
- Copy down. Filter column C for "CHECK:".
- Add each new spelling to CityMap. The CHECK rows fix themselves.
स्टेप बाय स्टेप करून बघा:
CityMapनावाचं table बनवा: columns Raw (lower case) आणि Standard.- C2 मध्ये वर दाखवलेला formula type करा.
- Lookup आधी LOWER आणि TRIM raw text साफ करतात.
- खाली copy करा. Column C मध्ये "CHECK:" filter करा.
- प्रत्येक नवं spelling CityMap मध्ये add करा. CHECK rows आपोआप ठीक होतात.
स्टेप बाय स्टेप करके देखें:
CityMapनाम की table बनाएँ: columns Raw (lower case) और Standard.- C2 में ऊपर दिखाया formula type करें.
- Lookup से पहले LOWER और TRIM raw text साफ़ करते हैं.
- नीचे copy करें. Column C में "CHECK:" filter करें.
- हर नई spelling CityMap में add करें. CHECK rows अपने आप ठीक हो जाती हैं.
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 |
About this data: Made-up rows so you can copy the pattern for inconsistent city spellings. Currencies may use ₹, £, € or $.
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 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
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 |
About this data: Teaching sample for a fictional shop “CityBasket Retail”. Figures and names (e.g. Neha Shah) are made up for this lesson on inconsistent city spellings.
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.
Loading practice tasks...
- Correct name for C2 (Easy): Find the correct city for C2 using CityMap.
- Correct name with VLOOKUP (Easy): Find the correct city for C4 with VLOOKUP.
- Correct name for C6 (Easy): Find the correct city for C6.
- Correct name for C8 (Easy): Find the correct city for C8.
- Fix the extra space first (Medium): Find the correct city for C3. Clean it first.
- Name not in the list (Medium): For C9 show the correct city, or "CHECK: Gurgaon" if not found.
- IFNA with VLOOKUP (Medium): For C9 use VLOOKUP, and show "CHECK" if not found.
- Is the name already correct? (Medium): Is C2 already the correct name? (TRUE or FALSE)
- Amount for Mumbai (both names) (Hard): Total amount for Bombay plus Mumbai (use TRIM so "Mumbai " counts).
- Amount for Bengaluru (both names) (Hard): Total amount for Bangalore plus Bengaluru.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
