Leverage Text Formulas to Get Advanced in Excel
Published: August 2, 2016
Author: Oliver Eldredge
Text formulas are a powerful tool in Excel. They force you to think about how you’re nesting formulas and often require a degree of creativity to get the results that you want. Learning these techniques will not only increase your ability to manipulate text strings in Excel, but will also increase your ability to nest other formulas and construct solutions to other complex problems.
Let’s start with the basics and go from there.
Simple Text Formulas
The =TEXT() formula is a great place to start. It can take a variety of inputs and crank out a wide variety of outputs. Its syntax is fairly arbitrary but reasonable to navigate when referencing a list of available options. Here are a few examples of the many different things that it can accomplish.
= TEXT(“7/4/2016”,”DDDD”) -> “Monday”
= TEXT (“7/4/2016”,”DDD”) -> “Mon”
= TEXT (“7/4/2016”,”MMMM”) -> “July”
Play around with H, M, and S to display hours, minutes, or seconds too.
Controlling Decimal Places, or Adding Commas and Symbols
=TEXT(123456.789,”$#,##0.00″) -> “$123,456.79”
=TEXT(0.4837876,”%0.00″) -> “48.38%”
You can also get several different formats of data converted into a text strings, which allows them to work much better when concatenated.
=TEXT(B2,”$#,##0″)&” in Sales on “&TEXT(A2,”D/M/YY”)
Search is an interesting three-parameter function that looks for one text string within another, and then returns a numeric value that corresponds with the location of the text string that you were looking for. Let’s take a look at an example.
=SEARCH(“pizza”,”Mike’s Pizza”) -> 8
This works because the string “pizza” shows up eight characters into the text string “Mike’s Pizza”. The third parameter can control how far into the text string you start looking for a match.
This formula works very well when nested into an =ISNUMBER() formula. The combination =IF(ISNUMBER(SEARCH()),value if true, value if false) ends up working something like what you would imagine an “IF CONTAINS” formula would.
This formula is more flexible than using a table and lookup, although it doesn’t scale particularly well. However, this technique works exceptionally well in situations with a limited number of potential outputs, and uniform but unknown inputs (an account that continually adds new campaigns with a reliable naming convention).
Left and Right are two more formulas that have many uses in digital marketing. Each of these will return the “x” number of characters from the right or left of a text string. On their own they aren’t terribly helpful, but when combined with =LEN() and =SEARCH() they can be used to extract information from a URL very reliably.
Play around with different formulas to take the total length of a string =LEN(), and subtract the number of characters to find a unique identifier =SEARCH(). You will likely have to account for the number of characters that were included in the string you were searching for, by subtracting a few extra characters to get the output you that want (-2 in the example above).
Learning to become proficient in these formulas is a great way to build your understanding of Excel beyond just working with numeric data. It also incorporates a lot of nested formulas and contrived solutions. These same skills can be applied many times over in other tasks in Excel that have nothing to do with text strings.