When merging data from different sources, company names are often inconsistent. Some are ALL CAPS, others lowercase. Some include "Inc." while others say "Corporation". Standardizing these names is essential for accurate matching and reporting.

In this exercise, you have a list of company names in various formats. Your goal is to clean them into a consistent, professional format.

The challenge

Each company name may have one or more of these issues:

• Legal suffixes: Inc, Inc., LLC, Corp, Corporation, Co.
• Leading "The": Sometimes included, sometimes not
• Inconsistent case: ALL CAPS, lowercase, or mixed

Target format

The standardized name should:

1. Remove all legal suffixes (Inc, Inc., LLC, Corp, Corporation, Co.)
2. Remove the leading "The " if present
3. Convert to proper title case
4. Have no trailing commas or extra spaces

Multiple ways to solve it

There are several valid approaches to this problem. Use any formula strategy you like as long as it produces the standardized names described above. You can rely on a single formula, helper columns, or different functions. Whatever feels most natural to you.

Your task

In column B (rows 2-11), write formulas to standardize each company name. Aim for a solution you can fill down from the first row to the last.

Examples:

• "ACME CORPORATION" → "Acme"
• "the johnson group, llc" → "Johnson Group"
• "Smith & Associates Inc." → "Smith & Associates"

Note: You may end up with a very large formula. This is expected.