Skip to content

Email Newsletter Challenge 9

This week’s challenge will test your ability to solve a data cleaning problem with a solution that is not so obvious. Think you can figure it out? Give it your best try!

Download the spreadsheet challenge using the links below.

The Challenge

This time, your challenge is to remove the information in the parentheses leaving only the stock ticker. You also want to make sure that there are no extra spaces left over.

The Solution – Excel

The solution for this challenge is different for Excel and Google Sheets. Here is the solution for Excel.

The easiest way to solve this specific problem in Excel is to actually use the Find and Replace tool.

Start by selecting the data in B4:B13, then go to Home > Find & Select > Replace. You can also use the keyboard shortcut CTRL+H.

In the “find what” box, type in the following. Note that there is a space before the first parentheses.

[space](*)

Leave the “replace with” box blank, and click on Replace All.

In the box that appears, click OK. And you are done with challenge number nine!

Why Did This Work?

Writing your find expression as [space](*) told Excel to search for:

  • Space
  • Open parentheses
  • Anything between the parentheses (The asterisk is used as a wildcard character)
  • Close parentheses

Then, since you left the “replace with” box empty, Excel simply replaced what it found with nothing. Therefore, the space after the stock ticker along with the parentheses and the contents in between are simply removed.

The Solution – Google Sheets

To solve this problem in Google Sheets. Select a cell in row 4 to the right of column B. In my example, I select cell D4. Then enter the following formula.

=LEFT(TRIM(B4),FIND("(",TRIM(B4))-2)

The TRIM function removes all spaces before and after the text in B4. It also removes all but one space in between each word.

=LEFT("AAPL (Apple Inc.)"),FIND("(","AAPL (Apple Inc.)")-2)

The FIND function then locates the position of the open parentheses within the text.

=LEFT("AAPL (Apple Inc.)"),6-2)

Finally, the LEFT function takes the first four (six minus two) letters from the text resulting in the stock ticker.

Once you have the first stock ticker, use the fill handle to copy the formula down to row 13.

Then select all of the calculated stock tickers, use CTRL+C to copy. Then select cell B4, and use CTRL+SHIFT+V to paste.

Finally, delete the formulas you wrote (which should now be errors). And you are finished with your ninth weekly spreadsheet challenge!

If you used a different method than I did, be sure to let me know in the comments below.

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