MID( ) is intimidating for such a short function.
But when you’re in the process of data “wrangling” — that is, transforming it for more useful analysis — it can be very useful.
Many numbers in accounting have symbolic meaning. In this example, the cost center has a numeric logic. The third and fourth digit indicate a store location.
To use VLOOKUP and find a matching store location, we need to take steps:
- Select the third and fourth values using MID( )
- Convert the resulting number to a number.
The MID( ) argument will ask you what number in the string to start and how many characters to move over.
Your resulting answer will be formatted as text. You may not realize that Excel cells can take different formats — currency, date, etc. Numbers and text are two different formats that are not the same.
19 and 19 look the same… but to Excel, one could be text and one could be number.
Since VLOOKUP depends on the same cell formats, you will not get an answer in our example.
Great workaround: multiply any cell formatted as text by 1 to convert it to number.
(This is good to know if you’re doing any kind of data exports from BI software packages — this data often comes formatted as text.)
Combine the steps and you’re able to lookup the cost center location!
Leave a Reply