UK date from a mixed format US date in Excel

Quite often if you open a CSV file in Excel with American date formats Excel will do its best to convert them (badly) and you end up with some in date format and some in text format.

This formula helps to sort it out.

DATEVALUE(IFERROR(MONTH(A2),SUBSTITUTE(MID(A2,FIND(“/”,A2)+1,2),”/”,””))&”/”&IFERROR(DAY(A2),SUBSTITUTE(LEFT(A2,2),”/”,””)&”/”&RIGHT(A2,2)))

So 6/21/16 becomes 21/06/2016  and 5/29/16 becomes 29/05/2016

How to open 2 copies of Excel at one time – very useful

It is quite easy and very useful if you are running complex and long calculations.

  1. Add Excel to you Start Menu. – In the program files right click Excel and chose “Add to Start Menu”
  2. Shift Click Excel on Start Menu – this opens a new version separate from any previous one.
  3. Add Excel to you Taskbar. – In the program files right click Excel and chose “Add to taskbar”
  4. Shift Click Excel on the Taskbar – this opens a new version separate from any previous one.

As far as I can tell you can only run two versions at a time.

Make your excel lookups 100 times faster using Match

This method is almost instant (using 500,000 record lookups)

IF(M56=INDEX(‘Sitemap Keyword Categories’!$G$1:$G$300016,MATCH(M56,’Sitemap Keyword Categories’!$G$1:$G$300016,1),1),”Supported”,”Not Supported”)

This does an “nearest match” (using status “1”) and then tests to see if the nearest match is correct (ie “M56=”).

This Updated 500,000 records in under 1 second – previous methods (exact match using “0” and vlookup function ) would take 5 minutes.

It is vital that the column G in “Sitemap Keyword Categories” is sorted A-Z

Breaking text up into words in excel formula – 3rd,4th etc

These formulas work well and are easy to adapt for any word in a string of text  – just change the SUBSTITUTE number through out the fomula to 1 less than the word you are looking for. The IFERROR returns a blank but it can be useful to return a character something like “^” instead for lookups.

4th
=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,3),FIND(“^”,SUBSTITUTE(A23,” “,”^”,3))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,3),FIND(“^”,SUBSTITUTE(A23,” “,”^”,3))+1,256)))),””)

3rd
=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,2),FIND(“^”,SUBSTITUTE(A23,” “,”^”,2))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,2),FIND(“^”,SUBSTITUTE(A23,” “,”^”,2))+1,256)))),””)

2nd
=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,1),FIND(“^”,SUBSTITUTE(A23,” “,”^”,1))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,1),FIND(“^”,SUBSTITUTE(A23,” “,”^”,1))+1,256)))),””)

1st
=TRIM(MID(A23,1,FIND(” “,A23&” “)))