The Best Web Host!!

Cleaning List of Names in Excel with a formula

Take A1 and remove “*” located at the end.

=IF(ISERROR(IF(FIND(“,”,A1)>0,A1)),A1,IF(FIND(“,”,A1)>0,IF(ISERROR(FIND(“*”,A1)),A1,LEFT(A1,FIND(“*”,A1)-1))))

If A1 = “Doe, Jane*” it will return “Doe, Jane”

Excel – reverse vlookup from bottom up. Love this, hard to find!

This will return the last match, assuming your lookup value is in A1 and your return results are contained in column D….change as necessary. =LOOKUP(2,1/($C$1:$C$5201=A1),$D$1:$D$5201)

This is like using a vlookup from the bottom up.

How to convert Time HH:MM:SS to Decimal Hours in Excel & Decimal Hours to Time HH:MM:SS

Time HH:MM:SS to Decimal Hours Formula ( 2 options) 6:30:00 6.5 “=HOUR(D12) + (MINUTE(D12)/60) + (SECOND(D12)/60/60)” Format cell to General 6:30:00 6.50 “=A15/0.041666667” Format cell to Number Decimal Hours to Time HH:MM:SS Formula 6.5 6:30:00 “=A15*0.041666667” Format cell to Time