Complex text parsing

Data rarely arrives in a clean, consistent format. In this exercise, you'll tackle a common challenge: parsing addresses that use different delimiters into structured columns.

You have a list of addresses in column A. Some use commas as separators, some use pipes (|), and others use semicolons (;). Your goal is to extract the street, city, state, and zip into separate columns.

The challenge

Each address contains four parts separated by a delimiter:

• Street (first part)
• City (second part)
• State (third part)
• Zip (fourth part)

The delimiters vary: , (comma), | (pipe), or ; (semicolon).

Key functions you'll need

• SUBSTITUTE: Replace one delimiter with another to normalize the format
• FIND: Locate the position of a character in text
• LEFT: Extract characters from the beginning of text
• MID: Extract characters from the middle of text
• RIGHT: Extract characters from the end of text
• LEN: Get the total length of text
• TRIM: Remove extra spaces from results

Strategy

1. First, normalize all delimiters to one type (e.g., convert | and ; to commas)
2. Then use FIND to locate delimiter positions
3. Use LEFT, MID, and RIGHT to extract each part
4. Wrap results in TRIM to clean up extra spaces

Your task

Fill columns B through E for all six addresses (rows 2-7):

• B: Street address
• C: City
• D: State
• E: Zip code

Build formulas that handle all three delimiter types. Results should be clean text without leading or trailing spaces.

Note: This is a tricky one. A large formula is expected!