We had a socially distant hangout with two kiddos from the neighborhood who wanted to come over and play with our delightful pet turtle, Prince. In between giving him a bath, feeding him worms, inspecting his shell, and passing him around, we also played a few rounds of hide and seek. First, they wanted to hide and have the turtle find them. This was easy enough to satisfy. However, I started to get a tad worried when they wanted us to hide the turtle and then go seeking. Hidden turtles might sound like fun for toddlers but it spells emergency in my book! Nevertheless, I think a good time was had by all.
Lo and behold, in my spreadsheet work, I also faced a hide and seek dilemma. I needed to use an a spreadsheet formula to search for words that may or may not be hiding inside of a cell.
I had a spreadsheet that included a list of pets, followed by a cell with favorite foods. Imagine, “lettuce, worms, cabbage” arranged like that, with commas. Instead of having a list, I needed to re-arrange the data so that there was a separate column for each food, and a checkmark if the person indicated that preference.
(Aside: if you’ve been following my blog for awhile, you might notice that this post is kind of the reverse of this post! Thank you to pals who wrote such great comments there, which helped give me direction as I tackled this new problem). Ok, now back to the main event:
As with many of these types of problems, there is more than one way to solve the problem. I could have used the split-string feature (sometimes called delimiter function) to spread out the types into their own columns. The same feature is also incredibly useful for splitting Full Name into First + Last or Honorific + First + Middle + Last if you really want to get fancy. It’s also a life saver when it comes to addresses if you need to divide up Street + City + State + Zip. We could spend a whole week just learning Delimiter, but that’s not how I solved this particular problem. So, keep reading if you want to explore some other Excel formulas for dealing with text.
Delimiter wasn’t the best solution (even though it’s REALLY useful!) because my goal wasn’t to separate the list into columns, but rather, to create a checklist based on the list. In other words, if Prince likes lettuce, add a checkbox, otherwise, skip and look at the next one. Delimiter could get me part of the way there, but it wasn’t the most elegant option.
Finders, keepers
Instead, I needed to use the good, ole =FIND() function. The FIND() function looks at the list and if it finds the word I’m searching for, it returns a number. The number represents the character in the list where the word started. Let’s look at an example, because this one is a bit tricky to explain.
- Prince’s preferred foods are “lettuce, worms, cabbage”
- I can use the FIND() function to figure out if Prince likes worms
- The function would look like =FIND(“worms”, “lettuce, worms, cabbage”)
- In other words, =FIND(“hidden word”, “list of words”)
- In this case, Excel would give us the number 10 because the “w” in “worms” is the 10th character in the list
- Since Excel gave me a number, I know that somewhere in the list, Prince likes worms!
Here’s where FIND() gets kinda annoying. If we look for the third food (cabbage), and FIND() doesn’t, well, find it, Excel will give us an error and return #VALUE!. Blargh. “Value” is not the answer I’m looking for!
We can write better formulas so that Excel doesn’t give us a bad #VALUE if it doesn’t find any results. (BTW, same is true in Google Sheets!). Let’s explore how to pair the IF() formula with the FIND() formula. Oh, and we’ll need to throw in ISERROR(), too.
The problem we need to solve is avoiding the #VALUE result. So that means that we need to tell the Excel formula engine that if there’s an error, do “x” and if there’s not, do “y.”
Look at how this formula compares the number in cell B2 ($5), compares it to the number 12 (in the formula), and then gives the result “OK” or “Over Budget.” Pretty nifty, huh?
Now, we need to take the same logic and apply it to the FIND() equation. If the FIND() equation produces an error (ISERROR() function), enter 0. If the FIND() equation produces a number, enter 1. The “1” will be our placeholder for a checkbox.
Returning to our previous example, let’s apply what we’ve learned.
=IF(ISERROR(FIND(“worms”, “cabbage, lettuce”)), 0, 1)
Excel will return “zero” if the error is true. It will return “1” if the error is false, aka the FIND() function works. Pretty sleek, huh?
=IF(ISERROR(FIND(“worms”, “cabbage, lettuce”)), 0, 1) = 0
=IF(ISERROR(FIND(“lettuce”, “cabbage, lettuce”)), 0, 1) = 1
See how I changed the search term, but kept the original list the same? You can do the same thing in your spreadsheet! This is how I was able to make columns for each food that Prince likes (or doesn’t like).
Yet another solution
In doing some research about visual representations of the IF() function, I stumbled upon this example (see above). It uses different Excel formulas … IF (), ISNUMBER() and SEARCH() to solve a similar problem with similar logic. Perhaps this is a topic for a different time, but there are some very interesting debates about when to use the SEARCH() function versus the FIND() function, which more or less do the same thing. If you want to go deep, check out this link. So much good stuff to learn!
So what?
So, we took a list and translated it into columns. As my dad would say, “that and 2.75 will get ya on the NYC subway!” (that is, unless you are participating in the fare strike). I appreciate your healthy skepticism. And I have some ideas about how we might apply this kind of logic to everyday changemaker activities. If you don’t actually deal with these kinds of problems, you have my full permission to call my bluff.
- You have addresses that are stored in one row and you need to separate into columns (Street, City, State, Zip)
- You have “full names” but you want to send an email to just “dear FIRST NAME” or you want to separate Honorific + First Name + Middle Name + Last Name + Jr/Sr etc
- You have a list of actions someone attended, and you want to send a follow up email/invitation to specific people based on action
- You use a “tagging” system in your database, and you export all people and all tags so that you can manipulate the data in a spreadsheet
- You are doing a fundraiser and your sign-up form stores people’s orders in a list, but you need to separate them so you know how many tshirts to order of each size
- Your organization is trying to splice together information from multiple systems, and they all kinda don’t work, and export/import data in different formats, and someone needs to figure it out, and that person might be you 😛
- OOPS did I just say that aloud? Wait, it’s only me who has that problem?
This is the kind of problem where it seems really abstract until you find yourself in one of those hellish scenarios (which I think are more common that we might think!) and then you are faced with hours of the tally-mark system unless you can make spreadsheets do the work for you. And ya know what? I absolutely think you can! If you need any help, you can always write me an email, and I might just blog about your spreadsheet solution.
***No turtles were harmed in the writing of this blog post.