Summary  
This chapter explains how to use a dynamic string substitution function to replace specific text within data cells and produce updated results without changing the original values.  

General domain of usage  
Data cleaning and normalization in spreadsheets

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.

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.

Use `=SUBSTITUTE(C2,"new york","New York")` and apply the formula to all rows.

What is the main advantage of `SUBSTITUTE` over Find & Replace?

Study how to clean, organize, and prepare data in Excel to ensure accuracy and reliability in your analysis. You'll understand why raw data is often messy and how improper formatting or errors can lead to incorrect results.

Dive deeper into the most common types of data issues, such as missing values, duplicates, inconsistent formats, extra spaces, and invalid characters. You’ll learn how to identify these problems and prevent them from occurring.

Learn how to clean and standardize text data in Excel to ensure consistency and accuracy in your datasets. You'll understand why messy text can lead to errors in analysis and how to fix it efficiently.

Learn how to work with numerical and date data in Excel, ensuring values are accurate, properly formatted, and ready for analysis. You'll understand how incorrect formats can break calculations and lead to misleading results.

Study how to identify and remove duplicate data in Excel to ensure your dataset remains accurate and reliable. You'll learn why duplicates can distort analysis and how to handle them effectively.

Using SUBSTITUTE for Dynamic Replacement

SUBSTITUTE Function