Hi – many businesses regularly receive external customer or supplier data and have the dilemma of how to utilise different data formats in their own reports.  I have seen quite a few businesses spend considerable hours manually re-entering data (monthly sales etc) in to their own management reports when it can be achieved in seconds!


With this in mind I have detailed below one of the first tricks to tidy up external data.  As external data is usually formatted consistently it often just needs something shortened or an element removed to be usable with a VLOOKUP or SUMIFS functions.  We can therefore utilise the LEFT, RIGHT, MID functions with the SEARCH function to do this.  There are other functions such as TRIM, CLEAN and SUBSTITUTE that I will explain next week.


 The example below shows a few ways you can tidy up an external product code/ref.  

Please feel free to download the example above.  I hope you find this post useful.