This formulas are very helpful and nor very popular. I used them very often:
Give current cell address
=ADDRESS(ROW();COLUMN()) - give current cell address;
Give current cell value
=INDIRECT(ADDRESS(ROW();COLUMN())) - give current cell value;
Search the text of B1 in the range $A$2:$A$415 with exact match. If you found the result return column 1 otherwise N/A.
In order the search to be propagated on all rows and not only on the current one you need to type this formula with CTRL + SHIFT + ENTER .
Searching for value of B14 in range $A$2:$A$15. You can use vlookup forluma instead of this one
Random number between two numbers
The formula generates random numbers between the min and max value. Values will be refreshed at every change in the excel sheet. If you want to keep the generated values paste them as values.
Count if found
search and count for word test
search and count for any word or digit
from YYYYMMDD to DD/MM/YYYY
"1" -> "00001" "23" -> "00023" "12345" -> "12345"
Convert to Java string
Very useful if you have large dataset in excel and you need to used it like list in Java.
text - > "text",
Convert to SQL string
Very useful if you have large dataset in excel and you need to used it like list in SQL.
text - > 'text',
Remove last charecter