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 2 - String manipulation using =CONCATENATE
As noted in Part 1 of our blog, Microsoft 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 Part 1 of this blog series we learnt the power of =LEFT, =RIGHT, = MID and =SEARCH to move data into separate columns in Excel following data extraction from your legacy software. Now the next issue to tackle is how to combine data strings, or strings and numbers, to make new strings ready for import into your new ERP software.
This is where the =CONCATENATE function comes in. Let’s imagine you have extracted a basic description of your stock items (typically 35 characters) from your legacy software and now want to enrich it with extra details to take advantage of the larger field sizes (typically 100 + characters) in your new ERP. For example, in your legacy system you have abbreviated details to save room, now you have an opportunity of presenting the text in full, making it easier to read on Sales Invoices and your website.
Let’s assume we want to combine the contents in Cell A1 with the Contents in Cell B1 to make one continuous string to store in Cell C1. The Excel formula you would enter into C1 would be:
=CONCATENATE(A1,B1).
We could copy this formula down the length of our data rows, and when complete to transform the formula to actual data we would right click on Column C, select EDIT Copy and then again on Column C select EDIT Paste Special Values.
What happens though if we want a space, or perhaps a dash to appear between the two joined pieces of data to make them easier to read? We can achieve this by modifying the formula to read: =CONCATENATE(A1, “-“,B1). Note the “-“ (quote marks dash) after the first comma, or if you wanted a space this would simply be replaced by a space “ “ between the quote marks.
Any character, or number, can be inserted using this methodology and as many columns as required can be combined by extending the formula as follows:
=CONCATENATE(A1, “-“,B1, F1, G1)”.
Again, once complete you can use the Copy/Paste Special values to convert the formula(s) into raw data.
One other function that can be useful when using =CONCATENATE is the ability to lock a part of the formula to a particular cell so that it does not automatically increment as you copy your formula into additional rows or columns. To lock a particular Cell, simply embed the “$” symbol on either side of the cell reference. For example to lock the first part of our formula to cell A1, the formula would look like:
=CONCATENATE($A$1, “-“,B1)
Alternatively, if we wanted to lock the formula to Column A but let it update the row number as we copied, we would only insert a $ to the left of the Column reference, for example
=CONCATENATE ($A1, “-“,B1)
Finally, if we had a list or lists from another Excel sheet (say a manufacturers catalogue) that we wanted to bring together into one data string, we could use the =CONCAT function. An example here could be where you have a list of ingredients in multiple fields and columns and wish to turn them into one log string so you can print a product allergen list label.
To do this we need to first name the range of data that we want to bring together. Lets assume its everything in Columns A,B with Columns C,D from Row 1 to Row 50. Using the cursor, we can highlight each range of cells and right-click, selecting Define Name, enter a name for each such as “DATARANGE” and “DATARANGE 2” then press OK. Then in cell E1 we could enter the formula
=CONCAT(DATARANGE1, DATARANGE2)
Cell E1 would then show one long string of the combined data. If we wanted to combine dash or space, then use the same arguments as before. An example here might look like
=CONCAT(DATARANGE1, “ “, DATARANGE2).
With these tools it becomes reasonably easy to clean up data. That means if you wish to move to a more powerful, all in one business software such as SAP Business One, you really don’t have to re-key your data.
A 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 3 next time.