Formula Friday #16
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
There are a handful of text functions that come in really handy when you need to restructure, or select, specific elements of data.
Let’s say, for example, you have been given a line number and you need to find out what the department and category codes are. We will assume in this instance that the department code makes up the first two digits of a line number, the category code is the last 3 digits and the style code is the middle 5 digits.
We will make use of the LEFT and RIGHT Functions to extract the data we need
The formula will look like this for the department code
=LEFT(B3,2)
Here Excel is returning the 2 leftmost characters from the line number
The formula for the category code will look like this
=RIGHT(B4,3)
Here Excel is returning the 3 rightmost characters from the line number
Now let’s tackle the bit in the middle
For this we will need to use the MID Function.
The formula to extract the style code, the middle 5 digits of the line number, will look like this
=MID(B5,3,5)
Here Excel is returning the five characters starting at, and inclusive of, the third character in the line number.
I would highly recommend testing out the MID Function yourself a couple of times. It will help you to get used to what it does. I always end up starting the count one too few, so in this example I initially had the formula as =MID(B5,2,5) and then realised the result wasn’t quite right and corrected it.
These three Functions are great on their own. And even better when they have been used in conjunction with an additional Function.
Going back to our previous example
We’ve been given a line number and were looking to extract the department and category codes.
Now that we know how to do that, we can take it one step further. We can now combine the formula we used to extract the department code with another function to be able to reveal the department name.
The lookup function, we will use for this, will be the VLOOKUP because it is the most common lookup function.
And this is what my formula looks like:
=VLOOKUP(VALUE(LEFT(D2,2)),G1:H4,2,0)
Probably a little different to what you were expecting. However, I am glad this happened as it prompts me to remind you that the LEFT, RIGHT and MID Functions are all text functions. By that I mean, the result produced by using them will be in text format. Even if the data they were referencing was a numerical value. I know, I know it’s a weird one but there is a way around it.
By nesting the LEFT Function inside the VALUE Function
I am telling Excel that I want the result of LEFT(D2,2) to be numerical. Because the lookup value in the table array G1:H4 of my VLOOKUP is formatted numerically, not as text.
If the lookup value in the table array was stored as text the formula would look like this.
=VLOOKUP(LEFT(D2,2),G1:H4,2,0)
Final thoughts
Using these text functions with a VLOOKUP, or a SUMIFS, is a really easy solution to those random queries you can sometimes get. Where you don’t have all the data you need but you know there must be a way to be able to do something to get you started. Like finding out which department to contact about a style that is doing well, or not so well, on Google Shopping for example.
Until next time
Ax
