You're an operations analyst loading 10 customer addresses into a system that requires the address split across four columns: Street / City / State / ZIP. The source has them concatenated in a single string with a consistent format:
Street, City, State ZIP
Example: "123 Main St, San Francisco, CA 94105" should split into:
- Street:
123 Main St
- City:
San Francisco
- State:
CA
- ZIP:
94105 (kept as a 5-character string to preserve any leading zeros)
The format is consistent across all rows: comma after street, comma after city, single space between state and ZIP.
Your task — fill four columns with formulas that drag-fill cleanly:
- B (Street):
=LEFT(A2, FIND(",", A2)-1)
- C (City): trickier — needs two FIND calls to find the second comma. Use
FIND(",", A2, FIND(",", A2)+1) for the second-comma position; MID slices between the two commas (with a +2 offset to skip ", ").
- D (State): the 2 characters starting at the position right after the second comma + 1 space.
- E (ZIP): the rightmost 5 characters of the string.
Wrapping each formula with LET to bind the comma positions makes the City and State formulas readable.
Graded cells: B3 ("456 Oak Ave"), C5 ("Boston"), D7 ("FL"), E9 ("30301").
Open this problem on a desktop to attempt it.