Using SUBSTITUTE for Dynamic Replacement

The SUBSTITUTE function allows you to replace specific text inside a cell while keeping the result dynamic. This means that if the original data changes, the result will update automatically.

SUBSTITUTE Function

The SUBSTITUTE function is used to replace specific text inside a cell with another value. Unlike Find & Replace, which changes the data directly, SUBSTITUTE works inside a formula and returns a new, cleaned result.

For example, if a cell contains the value "new york", you can use the following formula:

=SUBSTITUTE(A2, "new york", "New York")

Excel will take the original text from cell A2, find the part that matches "new york", and replace it with "New York". As a result, the formula will return "New York".

The important idea here is that the original data in A2 does not change. Instead, you create a new column with corrected values. This makes your solution safer and more flexible, because if the original data changes, the formula will automatically update the result.

Task

Create a new column called Standardized City.

Use the SUBSTITUTE function to standardize city names.

Replace all incorrect variations of "new york" with the correct format "New York".

Apply the formula to all rows to ensure consistency across the dataset.