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 4- Data manipulation using =VALUE, =TEXT, =PROPER and REPLACE
As noted in Parts One, Two and Three in this series of blogs, Microsoft Excel (“Excel”) is an ideal tool for cleaning data. Many basic accounting software 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.
In this blog we want to explore how we can tidy up existing data on mass when the data we have from our legacy system is just plain messy. Examples of this could be a mix of upper- and lower-case descriptions, spaces in text, numbers stored as text or text stored as numbers. It could just be as simple as getting some data consistency where an address may have AVE or AVENUE for the same meaning.
Let’s take a basic example where we extract our Customers name, address and phone numbers into a csv file and want to get some consistency before we import the data into our new ERP Software.
Imagine that the case typing of the same is all over the shop, some upper, some lower and some a mix of both. This is where we can use =PROPER to convert everything to upper case. If the name of the Customer appeared in Column A, then in Column B we could insert the formula
=PROPER(A1)
Then copy that formula down Column B for the length of the row data. This would then display the Customers name in upper case. We would then Highlight column B and use the Copy/Paste Special Value function to convert the formula to text data. Finally, we can delete the original Column A and leave only the ‘clean’ name as the new Column A.
Moving onto the address. Let’s imagine there are a series of inconsistencies in how common descriptors have been entered in the legacy data. This is where the =REPLACE function can assist. Let’s continue using our example of “Avenue” vs “AVE”, where the Street address appears in Column B of the data. We could get complicated and write a formula using =REPLACE, however that would require us to first work out what position in the text string the word we want to replace appears. A much easier solution will be to just use the standard Excel function that appears on the right of the home tab called ‘Find and Select’ / Replace. The icon on your home ribbon looks like this:
We simply highlight the data in Column B and then search for the text ‘Ave’ and replace with the text ‘Avenue’.
The last area we want to look at is the situation where we have a number recorded as text, or a number field that we need to convert to a text string.
For the first scenario, lets assume we have the post code recoded as part of the address line in our legacy data (column C) and in the new ERP there is a separate postcode field, however it requires the input data to be in numeric format. Using the =RIGHT function, we can first extract the postcode into a separate column. So cell D1 would contain the formula
=RIGHT(C1, 4) – the postcode being the last 4 digits of the address.
We already know from our earlier blogs we can use the Copy/Paste special/ Value feature of Excel to convert the formula in Column D to raw data – however here we have a problem - as the result will be a text field containing a number, not a numeric field as required.
To overcome this, we can use the function =VALUE to convert the text string to a number. In Cell E1 we would have the formula
=VALUE(D1)
Now using the Copy /Paste special / Value feature we can convert the formula contained in Cell E1 to numeric data.
In the second scenario, we would repeat the above process with the =TEXT function replacing the =VALUE function. So, Cell E1 would read
=TEXT(D1)
meaning it would take data with a native numeric value and convert it to a text string.
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 5 next time.
