Parse full address
IntermediateWhen you’re cleaning customer or lead lists, addresses often arrive as a single text field. Splitting them into structured columns (like city and state) makes reporting and filtering much easier.
In this sheet, column B contains full addresses in a consistent format:
<Street address>, <City>, <State> <ZIP>
Your goal is to extract only the City into column C.
Your task
- In C2, write a formula that returns the city from the address in B2.
- Fill the formula down through C12.
Use FIND to locate the commas and MID to return the text between them. The result should not include commas or extra spaces.
Need some help?
Answer
Exercise
Parse full address
IntermediateWhen you’re cleaning customer or lead lists, addresses often arrive as a single text field. Splitting them into structured columns (like city and state) makes reporting and filtering much easier.
In this sheet, column B contains full addresses in a consistent format:
<Street address>, <City>, <State> <ZIP>
Your goal is to extract only the City into column C.
Your task
- In C2, write a formula that returns the city from the address in B2.
- Fill the formula down through C12.
Use FIND to locate the commas and MID to return the text between them. The result should not include commas or extra spaces.