Finding Your Data

It is an obvious fact to any longtime Excel user that we do not always have control on the source of data, nor the format in which it is stored and exported.  There are times when we may receive data from third parties or other sources which are not under our direct management.  Due to this reality, an improved way to Find Your Data is a true boon to any analyst.

Let’s say that you wish to extract the dates from a less-than-ideal set of data.  For example, the Date Field may be mixed with text, spaces, symbols, or some combination.  So, what can you do with this unruly data?  Happily, Excel provides many possible solutions!

One way is to create a special formula that scours the field, and returns the date.  By using the MID, FIND, and DATEVALUE functions, most any irregular data can be handled with ease.  By adding in the IFERROR function, you can also keep your spreadsheet clear of any distracting error messages.

Putting this all together, creates the following formula:  =IFERROR(DATEVALUE(MID(B4,FIND( "/", B4 )-2,10)),"") which, as illustrated in the example below, neatly extracts the dates from the unconventional data contained in Column A:

There are, of course, typically numerous ways to achieve any result in Excel, and this is true in this case.  It is, however, one nifty little way to extract your dates from many types of unorthodox fields of data that you may have to work with in the future.  Try it some time when you need to Find Your Data!