Skip to content

Email Newsletter Challenge 6

This week will test your knowledge of text functions.

There are actually four different challenges this time. You can choose to solve one or all four. Either way, it’s up to you.

You can download the spreadsheet challenge using the links below.

The Challenge

Your challenge this week is to write formulas to extract different parts of the address.

  • Zip Code (Easy)
  • Street (Medium)
  • State (Hard)
  • City (Very Hard)

Zip Code – Solution

To extract the Zip Code, you want to capture the five characters at the end of the text string that makes up the address.

Start by selecting cell F5, and enter the following formula.

=RIGHT(TRIM(B5),5)

The TRIM function removes all extra spaces before and after the text string (address) in cell B5. Although this may not always be necessary, it’s wise to include the TRIM function just in case there are spaces you were not expecting.

Next, the RIGHT function captures the five characters from the right of the text string resulting in the zip code.

You can then copy the formula down with the fill handle to capture the zip codes for the remaining addresses.

Street – Solution

To extract the Street, you want to extract text from the left of the string until you come to the first comma.

Select cell C5, and enter the following formula.

=LEFT(B5,FIND(",",B5)-1)

The FIND function searches for the first instance of a comma within the text string in B5. It then returns the position of the comma which in this case is 16.

=LEFT(B5, 16-1)

The number 1 is subtracted from 16 , and then the LEFT function then returns the first 15 characters from the text string resulting in the street portion of the address.

You can then copy the formula down with the fill handle to capture the street portion for the remaining addresses.

State – Solution

To extract the State, you want to extract eight characters from the right of the address, and then extract two characters from the left of that result.

Select cell E5, and enter the following formula.

=LEFT(RIGHT(TRIM(B5),8),2)

The TRIM function removes all unnecessary spaces before and after the address. Then the RIGHT function extracts eight characters from the right.

=LEFT("IL 62701",2)

The LEFT function then captures the first two characters from the left to give you the state portion of the address.

You can then copy the formula down with the fill handle to capture the state portion for the remaining addresses.

City – Solution – Google Sheets

The solution for this last part is a little different for Excel and Google Sheets. Here is the solution for Google Sheets.

To extract the City, your first going to break the address up into three parts based on the two commas. You will then capture the second part and then remove all unnecessary spaces to get the final result.

Select cell D5, and enter the following formula.

=TRIM(INDEX(SPLIT(B5,","),2))

The SPLIT function breaks the address up into an array of three text values based on the two commas.

=TRIM(INDEX({"23 Maple Street", " Springfield", " IL 62701"},2))

The INDEX function then returns the second array item due to the 2 used as the second argument.

=TRIM(" Springfield")

Finally, the TRIM function removes any spaces before or after resulting in the city portion of the address.

You can then copy the formula down with the fill handle to capture the city portion for the remaining addresses.

City – Solution – Excel

Here is the solution for Excel.

To extract the City, your first going to break the address up into three parts based on the two commas. You will then capture the second part and then remove all unnecessary spaces to get the final result.

Select cell D5, and enter the following formula.

=TRIM(INDEX(TEXTSPLIT(B5,","),2))

The TEXTSPLIT function breaks the address up into an array of three text values based on the two commas.

=TRIM(INDEX({"23 Maple Street", " Springfield", " IL 62701"},2))

The INDEX function then returns the second array item due to the 2 used as the second argument.

=TRIM(" Springfield")

Finally, the TRIM function removes any spaces before or after resulting in the city portion of the address.

You can then copy the formula down with the fill handle to capture the city portion for the remaining addresses.

And that’s it for your 6th weekly challenge! I hope you had fun and learned something new.

And, if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top