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”
Tag Archives: Formula
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 |
GuruOfComputerWisdom.com 
You must be logged in to post a comment.