VALUE() of LEFT()
I have been in a lot of situation needing to convert a number came out from a string into actual numbers. Sometimes N() worked, sometimes not, others VALUE() worked, but …
GoLast to jump to last cell in a column
Edit 2021-07-12: I needed this formula to work in a worksheet having space in its name, so original one did not work, here is the new version: =HYPERLINK(“[“&MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-1)&”]'”&MID(CELL(“filename”,A1),SEARCH(“]”,CELL(“filename”,A1))+1,500)&”‘!B”&COUNT(B:B)+6,”Go Last”) Again, …
Worksheet name, dynamically using formula
Get the name of the active worksheet in active workbook, using formulas only This function needs to have workbook saved Then, you just paste below formula into any cell …
Extract current (Active) workbook name
I need that more often than I thought It is basically extract the full workbook name that we are in, with no folders, and with no extension =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-6) So, here …
CamelCase to Camel Case
Here is a good practice for combining functions to get a full smartsheet It basically add a space before any capital letter in a text, helpful when you have strings …
Hyperlink usage and HyperlinkOf UDF
The Hyperlink function is really powerful and yet not widely used. You can create a whole navigation system with Hyperlink fx or create a custom jump to link to take user …