One of the most common challenges and hurdles to overcome when taking on any new software, is how to cleanse data from your old system. Don’t let messy data stop you from moving to a more powerful software platform to run your business. This series of blogs will present 5 of the most used groups of Microsoft Excel features for cleansing your data that are easy to use and will save you time.
Part 3- String manipulation using =IF and =MID
As noted in Part 1 and Part 2 of our blogs, Microsoft Excel (“Excel”) is an ideal tool for cleaning data. Many basic accounting systems such as MYOB, QuickBooks, Saasu and Xero allow you to export master data to Excel directly, or at least to a text (.txt) or comma separated values (.csv) which can in turn be opened in Excel.
The challenge you have is that basic accounting software often have a limited number of fields within which you can store data, so often fields are used for more than one purpose or contain multiple pieces of information, which in a more powerful business software / ERP platform can be stored in separate fields.
In this blog we want to explore how we can find a piece of text in what could be a range amount of data and use it as a basis for building a whole new set of information ready for import into your new ERP.
Let’s take the example where we have a manufacturers list of known allergens which exist in all their sauce-based products. We now want to include that allergens list as part of the data we keep in our new ERP software. Further, we only want to have that list attached to Items which have a sales unit of measure of 1 litre.
Let’s assume the Item data we have extracted from our legacy software does not have a category called “sauce” - we can only work with the understanding that “sauce” exists somewhere in the product description. It does however have the unit of measure as a separate field. So, the issue we need to address is how do we easily find all the relevant sauce items and tag them with the appropriate allergen warning?
This is where the “= IF” function can assist. In essence it provides a conditional outcome – we can find all instances of the word “sauce” in the item description, and if that item also contains the word “1 litre’ in its unit of measure, then we can populate the allergens field with the appropriate allergen warning. If this combination of data is not found, it will leave the allergen field blank.
We will use the skills already discussed in Parts 1 and 2 of our blog series to find the word “sauce” in the item description. In the scenario where the Item description appears in Column A, the unit of measure appears in Column B, and our allergen advice appears in Column E, we would perform the following steps to achieve our outcome:
Step 1 – locate the word “sauce” in Item description.
In Cell C1 we would have the following formula
=FIND(”SAUCE”,A1)
This would return the text position within the description as a number.
In Cell D1 we would then use =MID to get the word “sauce” to appear as a word in that cell i.e.
=MID(A1,C1,5)
This formula says in cell A1, move to the text position number identified in cell C1 and then bring back the next 5 letters, the word “sauce” having 5 letters.
Finally, we would highlight Column D and use the copy / paste special values function to turn the contents of Column D into a text string, not a formula.
Step 2 – find which Items need to have the allergen advice populated using = IF.
The test would be that if Column B has the word “1 litre” and Column D has the word “SAUCE” then we should have the allergen advice appear in a cell in Column F, if not the cell should remain blank.
The formula in cell F1 would read
=IF((CONCAT(B1,D1)=”1 litresauce”),E1, “ “)
This formula uses the CONCAT function to join the contents of cell B1 and D1 to make a string, this applies the test to say that if the string equals “1 litresauce” return a true outcome it will write in the allergen, if not it will leave it blank.
With these tools it becomes reasonably easy to clean up data. That means if you wish to move to a more powerful, whole of business software such as SAP Business One, you really don’t have to re-key your data.
An SAP Business One Cloud Starter pack license is available for as little as $140ex GST per user per month. It comes with the ability to import Customers, Suppliers, Items and Price Lists straight from a Microsoft Excel spreadsheet. Sales pipeline, CRM, Cash flow management and Budgeting – these are just some of the areas that a move to using SAP Business One could help with improving how you manage your business.
With what you now know, you could be up and running in a software that not only manages what has happened in your business but gives you insight into what is about to happen in your business - in less time and effort than you might think.
Watch out for Part 4 next time.
