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 1 - String manipulation using =LEFT =RIGHT =SEARCH and =MID
Microsoft Excel (“Excel”) is the most widely used tool for cleaning data. Many basic accounting systems such as MYOB, QuickBooks, Saasu, Xero etc 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 has a limited number of fields within which you can store data. Fields are used for more than one purpose or contain multiple pieces of information, which in a more powerful business software platform can be stored in separate fields, making them much more useful.
Let’s take the example of a physical address, where the state and postcode are combined in the one field in your existing software and you have the opportunity to have them in separate fields in your new business software.
If the address was in column A of your excel sheet and the postcode was the last four digits of that address, you can use the =RIGHT function to extract the postcode to a new column all by itself, in this case Column B.
An example of this function would be the following formula in cell B1 (Cell A1 contains for example NSW 2127)
=RIGHT(A1, 4)
Cell B1 would then show the last 4 characters of cell A1, in this case the postcode. You could then copy the formula in B1 down the cells for each row of your data until you reach the last record. The next step would be to convert the formula to an actual number rather than the formula above. You would achieve this by highlighting Column B and selecting Copy, then right-clicking and selecting Paste Special Value. Column B will then contain the actual postcode values rather than a formula.
Next let’s address the State. We know that the State appears as the first 3 characters in Cell A1. Using the =LEFT function, similar to what we just did with the postcode, we can extract the State part of the address to appear in Column C.
An example of this function would be the following formula in Cell C1 (Cell A1 contains for example NSW 2127)
=LEFT (A1,3)
Using the copy function you would extend this for all of Column C. This would then show just the State in Column C. To convert the formula to be just the text data ready for import, you would use the Copy and Paste Special Value function described above.
Finally let’s address what to do when you want to extract a piece of data that doesn’t sit nicely to either the left or the right of a cell. An example may be the case where the suburb appears after the street name, however the street name is a different length for each row of data. All we have to work with is the street and the suburb which are separated by a space in the field.
To get to our goal what we need to first do is work out what position the space first appears in our address field. To achieve this, we use the search function. If the address was in Column A, an example of this function in column B1 would be
=SEARCH(" ",A1) [note the space between the two quote marks]
Column B would then contain a number representing the space position in the address.
We can then use the =MID function to extract the suburb which we know appears immediately after the space. An example of this in column C would be
=MID(B1,15) *NOTE the number 15 is just enough characters to cover the most letters in a suburb
What this means is that the instruction is to count the number of positions up until the space appears, then return the next 15 characters - which in this case will be the suburb part of the address. As with our earlier formulas, we would then use the Copy and Paste Special function to turn the formula into actual data read for import.
With these tools it becomes easy to clean up data and help you transition to an all encompassing system such as SAP Business One and avoid the annoying task of re keying in 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 2 next week when we talk about string manipulation using concatenate