5.5 Inconsistent City Spellings

By Ravindra Bagale Last updated: 25 September 2026

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 with Sambhaji 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

Find & Replace madhe Match entire cell contents tick na kelyas khup students cha "Pune" replace kartana "Pune City" cha "Pune City City" asa gondhal hoto. Tick nakki kara. Roj yenarya data sathi mapping table best – ekda banavla ki navin spelling fakt ek row add karun sodvata yete.

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.