Intro to the FIND Function Formula Friday #11 Formula Fridays...
Read MoreFormula Friday #16
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
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.
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
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.
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.
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)
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
Intro to the FIND Function Formula Friday #11 Formula Fridays...
Read MoreHow to use nested IFs Formula Friday #14 Formula Fridays...
Read MoreFinding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.
I can help bring your ideas to life. Let’s talk about what we can build together.
Cookie | Duration | Description |
---|---|---|
cookielawinfo-checkbox-advertisement | 1 year | Set by the GDPR Cookie Consent plugin, this cookie is used to record the user consent for the cookies in the "Advertisement" category . |
cookielawinfo-checkbox-analytics | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics". |
cookielawinfo-checkbox-functional | 11 months | The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional". |
cookielawinfo-checkbox-necessary | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary". |
cookielawinfo-checkbox-others | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other. |
cookielawinfo-checkbox-performance | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance". |
CookieLawInfoConsent | 1 year | Records the default button state of the corresponding category & the status of CCPA. It works only in coordination with the primary cookie. |
viewed_cookie_policy | 11 months | The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data. |
Cookie | Duration | Description |
---|---|---|
_ga | 2 years | The _ga cookie, installed by Google Analytics, calculates visitor, session and campaign data and also keeps track of site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognize unique visitors. |
_ga_EQQNC0TZ45 | 2 years | This cookie is installed by Google Analytics. |
_gat_gtag_UA_206306690_1 | 1 minute | Set by Google to distinguish users. |
_gid | 1 day | Installed by Google Analytics, _gid cookie stores information on how visitors use a website, while also creating an analytics report of the website's performance. Some of the data that are collected include the number of visitors, their source, and the pages they visit anonymously. |