
MID Formula
This post follows on from our post dated 2 June 2017 and deals with extracting data from the middle of a string of information. Previously we used a forename and surname and extracted the left part of the name. Now I've added a middle name so that the string of data is: Surname Forename Middle Name. If I want to extract the middle part of the name, Stephen in this case, we can use a MID formula. Using the formula: =MID(A2,FIND(" ",A2)+1,FIND(" ",A2)) it will extract all the i

Drop Down List Defaults to Middle or Bottom?
One of the quirks in Excel that we have come across is when creating a Drop Down Data Validation List ("Drop Down DV List") the listing does not start at the top but can start in the middle or even at the end of the list. This occurs when there are blank lines included in the list, whether they have been included to separate out different parts of the list or have been included to allow new names to be added in the future. The problem occurs even when the Ignore blank check b

Extract text before a space
Often when data has been downloaded in Excel there is information in a cell that you would like to split in to two different cells so that you can analyse the data further. Take the simple example of a list of forenames and surnames that are listed in a single column. If you want to sort this list by forename this is fine, however to sort by surname you will need to split the data in to two cells. Using the FIND formula, together with LEFT, RIGHT or MID will allow you to easi