In cases like this you will need VBA. The User Defined Function below will do this. It is not mine. It states that it was written by tigeravatar.
If you aren’t familiar with how to install one of these copy the VBA code below. Then in Excel press Alt + F11 function key. Click Insert > click Module. A VBA editing window will appear. Paste this code into that. Exit the VBA editor.
Then array-enter this formula in F2 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
And here is the UDF code. You will need to save your file as an xlsm macro-enabled file.
Bookmarks